Stored Proc Question : Why If Exisits...Drop...Create Proc?

[CK]

Hi All,
Quick question, I have always heard it best practice to check for exist, if
so, drop, then create the proc. I just wanted to know why that's a best
practice. I am trying to put that theory in place at my work, but they are
asking for a good reason to do this before actually implementing. All I
could think of was that so when you're creating a proc you won't get an
error if the procedure already exists, but doesn't it also have to do with
Compilation and perhaps Execution. Does anyone have a good argument for
doing stored procs this way? All feedback is appreciated.
TIA,
~CK

[Cimode]

It is a limited scope attempt for correctly implementing UNIQUENESS on
all table rows...If you want to understand why it is a *good* practice
you need to learn more about rules of relational modeling and
especially database design and definition of primary keys...
Look for "Introduction to Database Systems" from CJ Date (at Amazon)

Hope this helps...
CK wrote:

Hi All,
Quick question, I have always heard it best practice to check for exist, if
so, drop, then create the proc. I just wanted to know why that's a best
practice. I am trying to put that theory in place at my work, but they are
asking for a good reason to do this before actually implementing. All I
could think of was that so when you're creating a proc you won't get an
error if the procedure already exists, but doesn't it also have to do with
Compilation and perhaps Execution. Does anyone have a good argument for
doing stored procs this way? All feedback is appreciated.
TIA,
~CK

[Erland Sommarskog]

CK (c_**********@hotmail.com) writes:

Quick question, I have always heard it best practice to check for exist,
if so, drop, then create the proc. I just wanted to know why that's a
best practice. I am trying to put that theory in place at my work, but
they are asking for a good reason to do this before actually
implementing. All I could think of was that so when you're creating a
proc you won't get an error if the procedure already exists, but doesn't
it also have to do with Compilation and perhaps Execution. Does anyone
have a good argument for doing stored procs this way? All feedback is
appreciated.

Nah, the best is to use ALTER if the procedure already exists, because
then you retein permissions on the procedure. In SQL 2005, you also
retain depenency information.

The problem is that there is no CREATE_OR_ALTER statment, so a pure
SQL script for installation, is most easiest written with IF EXISTS DROP
CREATE. To use ALTER in a pure SQL script, you would have to have the
procedure code in dynamic SQL, and that would be very difficult to
maintain.

The really best practice is to have some client-side load tool that
reads the procedure code and then replaces CREATE with ALTER as needed.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[KJAmbrose]

Hopefully this person posted the answer below to the wrong original
question, because his answer makes absolutely no sense....

Cimode wrote:

It is a limited scope attempt for correctly implementing UNIQUENESS on
all table rows...If you want to understand why it is a *good* practice
you need to learn more about rules of relational modeling and
especially database design and definition of primary keys...
Look for "Introduction to Database Systems" from CJ Date (at Amazon)

Hope this helps...
CK wrote:

Hi All,
Quick question, I have always heard it best practice to check for exist, if
so, drop, then create the proc. I just wanted to know why that's a best
practice. I am trying to put that theory in place at my work, but they are
asking for a good reason to do this before actually implementing. All I
could think of was that so when you're creating a proc you won't get an
error if the procedure already exists, but doesn't it also have to do with
Compilation and perhaps Execution. Does anyone have a good argument for
doing stored procs this way? All feedback is appreciated.
TIA,
~CK