ALTER proc vs IF EXISTS DROP/CREATE

[semaj.remle 'at' gmail]

For files saved in source control, is it better to use code to DROP/
CREATE a procedure like this:
------------------------------------------------------------------
IF OBJECT_ID ('procName') IS NOT NULL
DROP PROC procName
GO
CREATE PROCEDURE procName
AS
BEGIN
--proc code

END

GO
GRANT EXECUTE ON procName TO role
GO
------------------------------------------------------------------
or is it better to use the opposite logic and create a stub proc (if
it doesn't exist) then run an alter statement like this:

------------------------------------------------------------------
IF (OBJECT_ID('procName') IS NULL)
EXEC('CREATE PROC [dbo].[procName] select ''stub''')
GO
ALTER PROC procName as select 'Proc Code'
GO
------------------------------------------------------------------

Is there any advantage to one way over the other?

[Roy Harvey (SQL Server MVP)]

On Fri, 25 Jul 2008 08:03:24 -0700 (PDT), "semaj.remle 'at' gmail"
<se*********@gmail.comwrote:

>For files saved in source control, is it better to use code to DROP/
CREATE a procedure like this:
------------------------------------------------------------------
or is it better to use the opposite logic and create a stub proc (if
it doesn't exist) then run an alter statement like this:

It depends on how you manage your source code. The strong advantage
of DROP/CREATE/GRANT is that it works best when stored in a source
control library, but of course that requires a very disciplined
approach. If anyone does a GRANT without updating the script that
will be lost the next time someone executes the out of date script.

On the other hand if ALTER fails for some reason the previous version
still exists. If CREATE fails, the procedure was already dropped and
you could find yourself scrambling to find a copy of the old version
to put back until the problem with the new once can be fixed.

Roy Harvey
Beacon Falls, CT

[Erland Sommarskog]

semaj.remle 'at' gmail (se*********@gmail.com) writes:

For files saved in source control, is it better to use code to DROP/
CREATE a procedure like this:
------------------------------------------------------------------
IF OBJECT_ID ('procName') IS NOT NULL
DROP PROC procName
GO
CREATE PROCEDURE procName
AS
BEGIN
--proc code

END

GO
GRANT EXECUTE ON procName TO role
GO
------------------------------------------------------------------
or is it better to use the opposite logic and create a stub proc (if
it doesn't exist) then run an alter statement like this:

------------------------------------------------------------------
IF (OBJECT_ID('procName') IS NULL)
EXEC('CREATE PROC [dbo].[procName] select ''stub''')
GO
ALTER PROC procName as select 'Proc Code'
GO
------------------------------------------------------------------

Is there any advantage to one way over the other?

I certainly advocate the latter for at least two reasons:

1) You can manage permissons seperately from managing the source
code itself. It's very difficult to manage permissions if you
them scattered all over the source code - not the least if you
need to change them.

2) DROP means that all dependicies are kissed bye-bye, so that you
no longer knows which other SPs that call this one.

As Roy points out, the latter also means that if the new script
fails, that the old version is retained. Whether this is a good
thing or not depends on where it happens, but in a development
environment it's likely to be a good thing.

--
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