TSQL Tips: The correct way to write your Scripts – ALTER PROC, don’t DROP & CREATE PROC

  • Article

SQL Management Studio has a very nice Scripting feature that generates scripts to DROP & CREATE your stored procedures, tables & other objects. This is handy, but not good for production systems. Most people use it, … to their peril. This post highlights the potential issues & shows a much safer alternative.

The Problem(s):

Issue 1:
If you are trying to patch the same database on multiple servers, like a branch deployment OR you are a software vendor & you release a new version of your code, you might face the problem that your customers may be running different versions. 
      “Version 2” customers will need the modified version of your stored procedures eg: an ALTER PROC statement.
      “Version 1” Customers didn’t have that stored procedure so they will need a “CREATE PROC” Statement.
Typically the solution to this is to see if the Stored Proc exists & Drop it if it does & then always Create the new one. <see code below>. But that approach often causes other problems (see issue 2). 

 -- Bad Practice
IF  OBJECT_ID(N'dbo.p_MyProc',N'P') IS NOT NULL
    DROP PROCEDURE dbo.p_MyProc
GO

CREATE PROCEDURE dbo.p_MyProc ...

Issue 2:

Database Objects have security & other objects related to them. When you DROP & then CREATE a stored procedure, you lose all the security permissions associated with it. With a table it is even worse, you are also dropping any Indexes, Triggers, possibly constraints that were added outside of the table creation statement, typically foreign keys. Not to mention any data in the table.

 

The Solution:

While talking to Tobias Ternstrom, a Program Manager in the SQL Engineering team, he suggested the following. It is so simple & elegant I had to share it with you & suggest it becomes your new “Best Practise”.

 IF OBJECT_ID(N'dbo.p_MyProc',N'P') IS NULL -- Doesn’t exist
    EXEC('CREATE PROC dbo.p_MyProc AS SET NOCOUNT ON;') -- Create empty stub.
GO
ALTER PROC dbo.p_MyProc -- Always alter 
AS
…

Why do I like it?

1. You don’t need to maintain 2 identical scripts, one to create the proc & the other to alter it.

2. It overcomes the restriction that “CREATE PROC” must be the 1st & only statement in a batch.

Enjoy

     Dave

Technorati Tags: TSQL,Batch Scripts,SQL Server

Comments

  • Anonymous
    December 17, 2009
    I dislike the ALTER version. If you use DROP/CREATE the permissions have to be included. This makes the DROP/CREATE the full implementation. ALTER means you have to have all the versions going back to the original to know what permissions it is supposed to have.

  • Anonymous
    January 21, 2011
    I agree, this alter proc format is difficult to maintain. Drop/create is the way to go. The permissions should be included below the create statement

  • Anonymous
    February 28, 2013
    It is easy to envision different permissions being applied in different eviornments. There fore Including permissions in the create statement make the script less portable. Using alter cleany avoids all permission issues. In the case where the script is intended to manage identical permissions across all enviornments then drop/create seems like fine answer.

  • Anonymous
    January 14, 2016
    The comment has been removed