Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 13383 | Views in the last 30 days: 11
 
Related Articles
FORUM

version difference SQL server 2000

version difference Sql server 2000

FORUM

Excel source null column issue

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

FORUM

Raw File Destination in a loop where OLE source is changing

Dynamically changing sources and destinations

ARTICLE

A Versioning system for SSRS

Use SSRS to be create its own lightweight report version control system

Tags
administration    
configuring    
database design    
miscellaneous    
programming    
sql server 7    
strategies    
t-sql    
visual basic 6    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones