|
|
Posted by Erland Sommarskog on 10/02/91 11:50
CK (c_kettenbach@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, esquel@sommarskog.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
Navigation:
[Reply to this message]
|