Tips ‘N’ Tricks – T-SQL – An Elegant way to CREATE or ALTER Stored Procedures in One-Go in SQL Server for Easy Maintenance
While working with Stored Procedures, we often modify the them, during the course of maintenance, as part of enhancements, refactoring, and bug fixes etc. Based on what I have seen in the past, the usual approach what people follow for modifying Stored Procedures (and other similar objects like Functions etc.) is to check if exists, then drop it (if exists) and create/re-create it with the modified CREATE PROCEDURE Script. Following are the drawbacks of using this approach:
Permissions associated with the object, like GRANT EXECUTE etc., are lost when we drop and re-create the Stored Procedure.
If ALTER PROCEDURE Script is given during maintenance (to apply changes) then, different Script needs to be given while Creating the Stored Procedure (on a new environment or while re-building an existing environment) and while Modifying the Stored Procedure.
If DROP PROCEDURE and CREATE PROCEDURE approach is used, then all the permissions previously present on the Stored Procedure need to be given again with the help of necessary scripts.
To address the drawbacks highlighted above, there is actually a very elegant way of Creating and Modifying a Stored Procedure using a Single T-SQL Script and the same script can be executed multiple on any environment – whether it’s a new environment or an existing environment and you are applying some changes to the Stored Procedure.
Below is a Sample Script which can be used to Create/Alter a Stored Procedure.
USE AdventureWorks GO IF OBJECT_ID('dbo.uspGetEmployeeDetails') IS NULL -- Check if SP Exists EXEC('CREATE PROCEDURE dbo.uspGetEmployeeDetails AS SET NOCOUNT ON;') -- Create dummy/empty SP GO ALTER PROCEDURE dbo.uspGetEmployeeDetails -- Alter the SP Always @EmployeeID INT AS BEGIN SET NOCOUNT ON; SELECT HRE.EmployeeID , PC.FirstName + ' ' + PC.LastName AS EmployeeName , HRE.Title AS EmployeeTitle , PC.EmailAddress AS EmployeeEmail , PC.Phone AS EmployeePhone FROM HumanResources.Employee AS HRE LEFT JOIN Person.Contact AS PC ON HRE.ContactID = PC.ContactID WHERE HRE.EmployeeID = @EmployeeID END GO
In the above script, first part creates an Empty/Dummy Stored Procedure/Stub if the Stored Procedure with the specified name in the specified schema does not exist. This is useful for the initial setup, when you are creating this Stored Procedure in a new environment.
The second part of the above script, Alters the Stored Procedure always – Whether it’s created in the first step or it existed before. So, every time you need to make some changes in the Stored Procedure, only the ALTER PROCEDURE section (second part) of the above script needs to be modified and entire script can be executed without worrying whether the Stored Procedure already exists or not.
I would recommend using this approach as a Best Practice, as it simplifies the maintenance of Stored Procedures. Similar approach can be followed with other similar types of objects in SQL Server.
Let me know what is the approach you follow and if you have a better approach that the one shown above by leaving a comment below.
Note: In this approach, we have assumed that the Permissions are given on the Stored Procedure using a Separate T-SQL Script.
Take a look at the other Tips and Tricks in this Series on Tips, Tricks, Techniques, and Shortcuts to Improve Productivity, and Design and Coding Skills.
Posted on May 29, 2012, in Best Practices, Code Snippets, Development Tips, SQL Server, T-SQL Snippets, T-SQL Tips, Tips 'N' Tricks and tagged Best Practices, Maintenance, SQL Scripts, SQL Server, Stored Procedures, T-SQL, Tips, Tips and Tricks, Tricks. Bookmark the permalink. 14 Comments.