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