Home > Uncategorized > Stored Procedure version control

Stored Procedure version control

Often, when you work on a large scale database-driven application, you end up with hundreds of stored procedures. As the project evolves, the stored procedures get modified over time, and you often loose older versions of stored procedures.

Although there are many ways to provide stored procedure version control, I opted for a simple approach.

If you have a stored proc called MyProc, before editiing it, create a new procedure called MyProc;2. then edit MyProc (which I shall refer to as MyProc;1 for clarity).

When you call exec MyProc, then MyProc;1 will be executed. and MyProc;2 will not appear in any lists of stored procedures in Enterprise manager or Query Analyser.

To view the source of an older version of a stored proc, you can no longer use sp_helptext, but you can say "select text from syscomments where
id=(select id from sysobjects where name=’myProc’)
AND NUMBER=2"

 

 

 

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: