Make changes to SQL Server stored procedures with batch editing

Make changes to SQL Server stored procedures with batch editing

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:

  1. Generate

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in January 2008

  1. 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 and CREATE

    Learn more about SQL Server stored procedures:
    statements in the same script, so that existing stored procedures with the same names can be preemptively dropped. A lot of people complained about this and, to the best of my knowledge, it was fixed in SQL Server 2005 SP2. If the inflexibility of the Script Wizard in SQL Server 2005 drives you crazy, you're not alone. Bill Graziano has created a third-party mini-tool named Scriptio to address some of these issues. It works with SQL Server 2000 as well.
  2. Make a backup of this new script. This is very important. Do not work on the original file.
  3. 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.
  4. 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.

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top