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]

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

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 "Introducti on to Database Systems" from CJ Date (at Amazon)

Hope this helps...
CK wrote:[color=blue]
> 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[/color]

[Erland Sommarskog]

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

CK (c_kettenbach@h otmail.com) writes:[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[KJAmbrose@gmail.com]

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

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



Cimode wrote:[color=blue]
> 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 "Introducti on to Database Systems" from CJ Date (at Amazon)
>
> Hope this helps...
> CK wrote:[color=green]
> > 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[/color][/color]