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"
