Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Version Control for Stored Procedures Expand / Collapse
Author
Message
Posted Tuesday, May 7, 2002 12:00 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 1:44 PM
Points: 6,783, Visits: 1,882
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
Post #4104
Posted Wednesday, May 8, 2002 11:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 2, 2013 2:43 PM
Points: 152, Visits: 33
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
Posted Thursday, May 9, 2002 5:08 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Tuesday, August 26, 2014 8:57 AM
Points: 8,369, Visits: 736
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
Posted Thursday, May 9, 2002 8:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Thursday, May 9, 2002 9:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 1, 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
Posted Thursday, May 9, 2002 1:00 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing 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
Posted Thursday, May 9, 2002 1:16 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing 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
Posted Thursday, May 9, 2002 2:11 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 1:44 PM
Points: 6,783, Visits: 1,882
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
Post #33326
Posted Thursday, May 9, 2002 4:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:57 PM
Points: 33,206, Visits: 15,361
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
Posted Thursday, May 9, 2002 8:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase 123»»»

Permissions Expand / Collapse