SQLServerCentral Article

Version Control for Stored Procedures


Last year Steve

Jones (or is it Jeve Stones?) did a great

series on version control. At the stage where tools are now, source code for

SQL just isn't fun. Or easy. Is there a better way? Not sure this is better, but

this week I'd like to discuss a technique that Sean,

Leon & I

used recently that has a bit different focus.

It started with an enterprise application that had to go from design to

production in 90 days, with a team of three developers. The new application was

to be a new UI over an existing schema, in places we would be able to add or

change things, in others we were stuck with it because of the number of legacy

applications we already had in place. The old app uses a total of about four

stored procedures - let me tell you that this is NOT the way to get great

performance. One of our main goals in building was to move as much code as we

could into stored procedures.

By the time we rolled out the first beta for testing, we already had fifty or

more procs. Fairly well organized, lightly commented, but a good initial

structure. That was on about day 30. By the time we hit our final roll out date

2 months later,  we had done more than a hundred builds, almost everyone

having at least one new or changed procedure. To make matters a little more

complex, we had a test db that we used for development, then we would deploy our

changes to yet another test database. We had about 10 people testing different

portions of the app, often with different versions.

We used VSS for the application code (two projects, one UI and one dll), but

NO source control for the procs.

Amazed? Astounded? Aghast? Im out of "a" words, so I'll continue.

If the tools had better integration we probably would have put them in source

control, but as Steve discusses in his series it's not that easy. Not

impossible, but takes a bit of work AND a system. We didn't really have a system

in place that we were used to for team development. Growing pains of a small

company I guess!

Anyway, what we decided to do was anytime we changed a procedure, we

versioned it by creating a new proc and appending the version number to it,

something like this:

Create proc usp_test @param

varchar(12) as

--blah blah

Then when we versioned, the next

one would look like this:

Create proc usp_test1 @param

varchar(12), @other param varchar(10) output

--blah blah

Some of the procs never got changed. Others wound up having 10 versions. The

nice part was that any point we could look back in VSS and see which version of

the proc we were using at the time. Even better, we could have many different

versions deployed and working at the same time, all while we continued to make

changes based on test results. Worked pretty well for a low tech solution. More

importantly, we didn't have to spend a lot of time managing it.

Now to digress a bit, a couple weeks ago I finished reading SQL

Server 2000 Performance Tuning and came across a trick that I mentioned in

the review I wrote - and that is interesting enough that I'd like to repeat it

here. You can use a little known feature of SQL (little known to me anyway!) to

create multiple versions a different way, like this:

Create proc usp_test as ..........

Create proc usp_test;2 as .........

If you do this, you wind up with the source code for both (or more) procs in

syscomments. You can just call usp_test and it calls the original/default proc,

or you can qualify it as usp_test;2 to execute the later one. It's interesting,

but I think confusing to administer since you only see one proc in Enterprise

Manager or Query Analyzer. You have to look at the source to see that there are

multiple versions.

Would I use the same system again? You bet! At the end of the development

cycle it had never caused us a problem and turned out to be as efficient as we

could have hoped. I'm looking forward to your comments on this one:-)


1 (1)

You rated this post out of 5. Change rating




1 (1)

You rated this post out of 5. Change rating