I just saw a blog post by someone discussing the pros and cons of DROP/CREATE vs ALTER for changing a stored procedure. I was not able to comment on the blog post (seems like an error with their CMS) but I wanted to mention some issues with it and some better solutions.
I generally use DROP/CREATE most often in my work, but I'm trying to change that.
- If you have Zero Downtime requirements you risk spurious errors using DROP/CREATE. In the time it takes to drop the proc, recreate it, then issue the GRANTs your online users could see errors
- The author mentioned that using ALTER requires dynamic sql. That's not true, below I show you the pattern I use. Dynamic SQL is difficult to use and read for many developers. Doubling up quotes tends to be buggy.
- Oracle has the "CREATE or REPLACE PROCEDURE" syntax to avoid all of this. I wish MS would give us this. We would not need to do existence checking in metadata tables before creating or altering a routine.
In any case, this is the pattern I use that is very close to Oracle's CREATE or REPLACE PROCEDURE syntax.
sql server data architecture