SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Version Control for Stored Procedures

By Andy Warren,

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:-)

Total article views: 13425 | Views in the last 30 days: 3
Related Articles

version difference SQL server 2000

version difference Sql server 2000


Generate add / alter script for missing / different type columns

This query generate add/alter script for missing or different columns. Execute on source database an...


Excel source null column issue

Excel source columns are different when edited with different versions of excel


Raw File Destination in a loop where OLE source is changing

Dynamically changing sources and destinations

database design    
sql server 7    
visual basic 6