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

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

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.
ALTER PROC dbo.p_MyProc -- Always alter 

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.



Technorati Tags: ,,
Comments (4)

  1. Rick Lively says:

    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.

  2. Charles says:

    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

  3. Karl Kieninger says:

    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.

  4. Jo Ma says:

    In most cases SP permissions do not need to be (should not be) granted on individual SP by SP basis.  That is what security roles are for. You create roles that have execution rights. eg. You can create a 'db_execute' database role and grant it execute on every SP.  Then whoever has this role can execute any SP;  and of course you can be more granular.  This means that technically it is not necessary to include any GRANTs with any SP definition, which means the DROP/CREATE approach for SP definition is perfect and cleaner, and the whole point is rather moot.

    Even more, if you do not follow the role based approach, another approach is to abstract the "security configuration" for any database to a separate script that will execute all the grants necessary for the database to function properly based on the environment.  This makes it really easy to manage your security configuration in a single script.  And when you have a problem you can just re-execute this security script which many times fixes the problem.