Wednesday, May 07, 2008

Should SQLServer Have The CREATE [OR REPLACE] PROCEDURE Syntax?

I was asked by a developer at work the other day why SQL Server does not have the create or replace syntax. I started thinking and there were some advantages and one big disadvantage

First the advantages

Advantage
When scripting out a database you don’t have to generate if exists.....drop statements

When modifying an object from a file in source control you don’t have to change back and forth between CREATE and ALTER. This is really annoying sometimes; usually you create a proc or script out a proc and store it in Subversion/SourceSafe. Now you take that file, make some changes to the proc and run it. Of course it will tell you that the proc already exists, in general you don’t want to drop the proc and then execute the CREATE proc statement because then you have to worry about the permissions.(not everyone is running as dbo you know)

Disadvantage
I can overwrite a proc without even knowing it. What if we both are working on a proc and somehow we pic the same name I create my proc, you work in a remote location, you create yours and mine is gone.

Of course it is all what you are used to, I am sure the Oracle guys are not overwriting each other’s procs every day either

So what do you think? Would you be in favor of this syntax or not? Can you think of more advantages or disadvantages?

3 comments:

Don said...

I think that the disadvantage argument isn't fully thought out. Consider that an existing procedure is unknowingly being worked on by two different people. Both are going to code the script as IF EXISTS DROP PROCEDURE; CREATE PROCEDURE. There's no difference in doing it this way or having a CREATE OR REPLACE PROCEDURE. If there is a difference, I'd like to hear what it is. Semantically, it's the exact same from the two users perspectives. They both think that they're the rightful owner and as such, have a right to blow away the current version.

SQL said...

Don,

you are right and I don't think a lot of senior developers will fall into this 'trap'. I have seen plenty of code written by developers not using if exists, FWIW I used to do it myself when I started

Scott Whigham said...

I can think of no logical reason to keep it out. I posted it on the old SQL 2000 wishlist lo these many years ago but alas...