When writing a script to change a piece of code, say a stored procedure or view, there are 3 basic options.  I’ll start at the outset by saying I use all 3 and that they each have their pluses and minuses.  I’m not trying to advocate one or another, just describe each so you can make an informed decision which to use.   An important thing to remember that affects all of these methods is the fact that CREATE and ALTER statements have to be the first statement in a batch.

First DROP and CREATE.  This method involves first checking to see if an object already exists and if it does exist dropping it.  Next you can create the object freely.  The script will be similar to this:

IF OBJECT_ID(‘MySP’)>0 THEN
     DROP PROCEDURE MySP
GO  -- Required so that the CREATE is the first command of a batch.
CREATE PROCEDURE MySP …..

Upsides: Simple to code and maintain.  It doesn’t matter if the object does or does not exist.

Downsides: Any specific permissions to the object are going to be lost.  By specific permissions I mean granting permissions to the object, not to the schema or database.  This happens because when the object is dropped and re-created it is assigned a new object id.  This object id is used for references between the various system tables/views.  This won’t affect code that refers to the object because those references are done by name which of course isn’t changing.

Next: ALTER.  Alter is the simplest way to change the object.  In this method you just “alter” the code.

ALTER PROCEDURE MySP ….

Upsides: This is the simplest of the three methods, although admittedly not by much.  Any specific permissions to the object are maintained.  See above for why.

Downsides: Only works if the object already exists.  This means that this method may not work with automated processes used to push code onto an instance.

Last and certainly not least involves using an IF statement to check if the object already exists and use CREATE if doesn’t or ALTER if it does.  Now if you remember from above where I said that CREATE and ALTER statements have to be the first thing in the batch this may seem a bit difficult.  You can’t have an IF then a CREATE like this:

IF OBJECT_ID(‘MySP’) > 0
     CREATE PROCEDURE MySP ….

So how do you do it?  Dynamic SQL of course.

 IF OBJECT_ID('MySp') IS NULL
     SET @sql = 'CREATE MySp ...'
ELSE
     SET @sql = 'ALTER MySp ...'
EXEC sp_executesql @sql

Upsides: This is the most flexible method.  The object is created when it needs to be and altered if it already exists.  This means that if it already exists any changes won’t affect existing permissions, and yet it can still be run when the code doesn’t exist yet and not give an error.

Downsides: This is by far the most complicated method to implement and to maintain.  I’m not going to go into any detail as to why.  If you’ve ever done anything more than basic dynamic SQL you already know.  If you haven’t you’ll find out.