Version Control for Stored Procedures

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Andy Warren » Version Control for Stored Procedures

30 posts, Page 1 of 3

1 2 3 »»»

Version Control for Stored Procedures

Rate Topic

Display Mode

Topic Options

Author

Message

Andy Warren

Posted Tuesday, May 07, 2002 12:00 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #4104

Ben Bolte

Posted Wednesday, May 08, 2002 11:50 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 26, 2012 12:24 AM
Points: 152, Visits: 31

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.

Post #33320

Antares686

Posted Thursday, May 09, 2002 5:08 AM

SSCrazy Eights

Group: Moderators
Last Login: Tuesday, April 09, 2013 12:53 PM
Points: 8,357, Visits: 684

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)

Post #33321

Eve

Posted Thursday, May 09, 2002 8:59 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 27, 2007 11:29 AM
Points: 132, Visits: 2

I like this article and started implementing suggested approach in my SQL development.

Post #33322

CurtM

Posted Thursday, May 09, 2002 9:53 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, June 01, 2012 1:40 PM
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.

Post #33323

ians

Posted Thursday, May 09, 2002 1:00 PM

SSChasing Mays

Group: General Forum Members
Last Login: Thursday, July 22, 2010 9:06 AM
Points: 632, 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

Post #33324

ians

Posted Thursday, May 09, 2002 1:16 PM

SSChasing Mays

Group: General Forum Members
Last Login: Thursday, July 22, 2010 9:06 AM
Points: 632, 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.

Post #33325

Andy Warren

Posted Thursday, May 09, 2002 2:11 PM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #33326

Steve Jones - SSC Editor

Posted Thursday, May 09, 2002 4:40 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 11:20 AM
Points: 31,437, Visits: 13,752

Intersting idea. Not sure I like this over my method :), 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

Post #33327

redhanded

Posted Thursday, May 09, 2002 8:36 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, August 12, 2003 12:00 AM
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.

Post #33328

« Prev Topic | Next Topic »

30 posts, Page 1 of 3

1 2 3 »»»

Permissions