Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Version Control for Stored Procedures


Version Control for Stored Procedures

Author
Message
Andy Warren
Andy Warren
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: Moderators
Points: 8206 Visits: 2712
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/versioncontrolforstoredprocedures.asp>http://www.sqlservercentral.com/columnists/awarren/versioncontrolforstoredprocedures.asp

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Ben Bolte
Ben Bolte
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 34
Good article! I was "forced" to start doing this when I needed to modify the parameters (interface) on procs for a new version of a UI. Without a staging server for new app versions, this has allowed me to do final "production" tests of apps without disrupting usage of the existing version.



Antares686
Antares686
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: Moderators
Points: 8834 Visits: 780
The mention of grouped procedures is a plus in your article. However I do not use for version control. For me these are queries that I related to one another to avoid dynamic SQL and the primary or ;1 is the controlling factor which takes all inputs and routes to the correct ;x item. They are quite usefull and do store their own query plans. In addition when I need to drop them from my system I just call by the name of the procedure group and not by the numbered value unless I need to drop only one. Another method I have seen thou is to use the 0.0.0 standard noted inside the code itself (Major.Minor.Revision) but for us we have not run into a need to do so yet. Good article.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)



Eve
Eve
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 2
I like this article and started implementing suggested approach in my SQL development.



CurtM
CurtM
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 13
A much needed topic indeed, Did you have any problems with recompiling the code that calls the procs? It seems that by renaming the proc, you now have to go out and find all the places that use the proc and update the name there too. As brbolte mentioned, this may be perfect when you don't want to break existing code, but for the stuff that we do, I would have to then modify dll's and redistribute the dll's to the team members, a lot of work, and it seems to me reduces one of the great qualities of SPs, the fact that you can encapsulate logic and just update the db instead of redistributing executables.

I will share my method, which is also some work, but perhaps less depending on the environment you are in. We use CS-RCS, and just use the scripting tools of Query Analyzer to save off a copy to be versioned. I think this would be more difficult in the hectic nature of the project you were on, but it works well, especially tracking changes as they migrate to a centralized testing db, or even just to production. The nice thing about using a revision control system is we can quickly see history and perform diffs on the files to see what is going on.

my 2 cents.



ians
ians
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 79
quote:

In addition when I need to drop them from my system I just call by the name of the procedure group and not by the numbered value unless I need to drop only one.

It's not possible to drop an individual stored procedure from a numbered group. They can only be dropped as a group



ians
ians
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 79
Interesting article, but as someone else mentioned, it has the disadvantage of needing to modify all occurences of where the stored procedure name is used.

An alternative is something like :-


create procedure aaa
as
exec aaa;2
go

create procedure aaa;2
as
select 'first version'
go

exec aaa returns 'first version'

exec aaa will always run the lastest version of the stored procedure

if we want to modify the stored procedure, but keep the old version for reference, we can do this :-

create procedure aaa;3
as
select 'second version'
go

alter procedure aaa;1
as
exec aaa;3
go

exec aaa now returns 'second version'

Obviously this will only work if you dont need to change the parameters to the stored procedure between versions.

You could just as easily use names with '_<number>' as grouped stored procedures, which allows you to drop older versions if you build up too many.



Andy Warren
Andy Warren
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: Moderators
Points: 8206 Visits: 2712
Good point about finding instances of it to change the name. For EVERY build we sent out a new dll and a new exe, even if there were no changes to one or the other. Used MSI package for the distribution. We did have to change the proc name in code, but we're pretty hard core about code reuse, normally a proc will only get called in once place, so it's up to the developer that changes the proc to update the source code at the same time (a good find/replace add in is handy). For the most part the procs were stand alone, but we had a few instances where a call to proc1 would branch to one of 5-6 other procs. If you versioned one of the sub procs you had to version the main proc as well.

Thanks everyone for their comments so far, figured I'd catch hell for such a low tech technique!


Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: Administrators
Points: 42504 Visits: 18877
Intersting idea. Not sure I like this over my method Smile, but it's worth a look. Nice way to obfuscate things if you are delivering code to a customer as well.

Steve Jones
steve@dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
redhanded
redhanded
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1
I've begun using the free version of SQL Programmer 2001 from BMC Software. It took a while to figure out how to get VSS going but now I have full version control over my stored procs. What I cannot do is prevent someone from changing stored procs in the database on the fly. But I can refresh using VSS to make sure the latest known version is in the database. I love this product and for the price it cannot be beaten.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search