The database system that I maintain makes extensive use of stored procedures. Most of the common tasks performed with the database are handled exclusively by 2000 and 2005 stored procedures through SQL Server stored procedures. It's often possible to make global changes to the way the system behaves by simply editing one stored procedure in SQL Server. However, sometimes you need to change a whole slew of stored procedures all at once -- which is the kind of surgery you want to perform as delicately as possible. This, of course, assumes you're not using Visual Studio or some other IDE to perform this kind of work, which takes a lot of the drudgery out of it.
Here is my basic approach to making changes to a group of stored procedures in SQL Server:
- Generate a SQL script that creates all of the relevant stored procedures in the database. Make
sure the script performs any needed DROP FUNCTION commands as well, so any existing copies of the
stored procedures are removed. The quick-and-dirty way to do this is to just use the Generate SQL
Script context menu option for a database in SQL Server 2000 Enterprise Manager. In SQL Server
2005, it's the Generate Scripts option in the Tasks context menu option for a database.
Note that the SQL Server 2005 Script Wizard looks very different from older versions, and if you're running SQL Server 2005 SP1, there is a small omission that may make repeated operations of this kind problematic.
- Under "Script Behavior," the only options are "Generate DROP statements
only" and "Generate CREATE statements only." In other words, there's no option to generate DROP
Learn more about SQL Server stored procedures:
- Make a backup of this new script. This is very important. Do not work on the original file.
- Open the script in your favorite text editor. The more complicated it is to do the search-and-replace work, the more probable it is you'll want to use an editor that has regular expressions. Even something as simple as Notepad2 will do the job most of the time. I strongly recommend an editor for your stored procedures that performs T-SQL syntax highlighting. Then, if you make any syntactical mistakes, they will be flagged before you attempt to re-import the script.
- Merge back in the new, edited script and pay attention to any errors in the stored procedure function. If something fails, you can always use the backup script you made to restore the procedures to their original state. I'm leaving out any instructions regarding things like database versioning since those could vary immensely from site to site.
ABOUT THE AUTHOR
Serdar Yegulalphas been writing about computers and information technology for more than 15 years for a variety of publications, including InformationWeek and Windows Magazine.
This was first published in January 2008