Alter Procedure VS Drop Create

I had to go back through this article and break it out in terms of Pro’s and Con’s.

Pro’s of using Alter Procedure

1. Permissions stay intact
2. ObjectId’s of procedures stay the same
3. Altering a procedure is transactional, dropping is not
4. The create date of the procedure remains as original, and modified date gets updated

Pro’s of using Drop / Create for a Procedure

1. Objects can be rebuilt in a new location using source control (does not need to already exist)
2. You don’t need to worry if the procedure already exists to determine how to write it

I’ve mostly used drop create in places where I’ve worked, but I’ve just been convinced to use Alter (where applicable) instead.

The script below will show the original procedure’s create date and modified date:

[cc lang=”sql”]
SELECT CREATED, LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES
[/cc]

As for the permissions issue, two things come up. The first is that I don’t set my permissions at the object level. I set them actually at the schema level (for 2005+). This comes in handy because whatever schema you create an object under, automatically inherits those permissions. However, if you have a DBA that sets more granular permissions on the objects, then drop/create will get you in trouble, because those permissions will forever be lost.

Another issue with drop/create, is that in a highly transactional system, if you execute the drop statement and a call comes in prior to the create, you will receive errors. Alter will not do this however because it will acquire a schema lock prior to modifying the procedure.