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.

Advertisements

About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

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 , , , , , , , , . Bookmark the permalink. 14 Comments.

  1. Great tip. I have seen a similar solution on Stack Overflow here: http://stackoverflow.com/a/2885664/550350

    But your method with using OBJECT_ID is much cleaner. I am adding this to my Create Stored Proc template. Thanks!

  2. Thanks Greg for the feedback. I am glad you liked it!

    • akismet-a956186fbb0f8ace6f6b69d5c5781dc5

      I implemented this approach in my tsql templates here: https://github.com/DynamicsGPDev/tsql-templates. How would you recommend using this approach with a create view script? Here is what I came up with:

      — check if view exists
      IF OBJECT_ID(‘dbo.vwEmployee’) IS NULL
      — create dummy/empty view
      EXEC(‘CREATE VIEW dbo.vwEmployee AS SELECT 0 AS A;’)
      GO

      ALTER VIEW dbo.vwEmployee AS …

      Please let me know if there is a better way to do this with a view. Thanks for your help.

  3. What about any GRANT rights ? Usually they’re added in the CREATE, not again and again in the ALTER ?

  4. too good ……..

  5. Very nice, but this does not work with views, and I don’t really want to drop it. I causes all sorts of problems. Any advice?

  6. Excellent. Really helps. Thank you for sharing.

  7. For functions, this works:

    — Check if Function Exists
    IF OBJECT_ID(‘dbo.fnEmployee’) IS NULL
    — Create dummy/empty Function
    EXEC(‘CREATE FUNCTION dbo.fnEmployee () RETURNS bit AS BEGIN RETURN 1 END’)
    GO

  8. How does it work for functions ?

  9. Execution rights should be granted via roles, not directly on SPs; then dropping and creating is never a problem. eg. You can create a ‘db_execute’ database role, and grant it execute on everything, then whoever you assign that role will have execution rights.

  10. Jairo Martínez

    Excellent trick!!!, many thanks…

  1. Pingback: Tips ‘N’ Tricks – Tips, Tricks, Techniques, and Shortcuts to Improve Productivity, and Design and Coding Skills « Datta's Ramblings on Business Intelligence 'N' Life

  2. Pingback: Tips ‘N’ Tricks – T-SQL – An Elegant way to CREATE or ALTER User Defined Functions in One-Go in SQL Server for Easy Maintenance | Datta's Ramblings..

What are your thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: