VB and VBA Users Source Code: Changing Existing SQL Stored Procedures

VB/VBA Source Code
Changing Existing SQL Stored Procedures

By:	Emlyn Phillips
Email (spam proof):	Email the originator of this post
Date:	Monday, February 12, 2001
Hits:	513
Category:	Database/SQL/ADO

Article:
Stored procedures cannot be modified in place so you're forced to first drop the procedure then create it again. Unfortunately there is no ALTER statement that can be used to modify the contents of an existing procedure. This stems largely from the query plan that is created and the from fact that stored procedures are compiled after they are initiated. Because the routines are compiled and the query plan relies on the compiled information SQL Server uses a binary version of the stored procedure when it is executed. It would be difficult or impossible to convert from the binary representation of the stored procedure back to English to allow for edits. For this reason it's imperative that you maintain a copy of your stored procedures in a location other than SQL Server. Although SQL Server can produce the code that was used to create the stored procedure you should always maintain a backup copy. You can pull the text associated with a stored procedure by using the sp_helptext system stored procedure. The syntax of sp_helptext is as follows: Sp_helptext procedure name
Terms and Conditions
Support this site Download a trial version of the Excel Workbook Rebuilder