Re: ALTER proc vs IF EXISTS DROP/CREATE

[Eric Isaacs]

Optional Create and Alter is nice because it fails, the original is
still in place as are the original permissions. Drop and create is
also valid in some cases if you want to clear/reset the permissions.

I use an approach which lets me choose which one I want to implement
on the fly. If I remove the first two dashes, the drop is disabled
and it creates only if it doesn't already exist. Otherwise it drops,
creates a filler sproc, and then alters with the real sproc...

--/* remove the two dashes at the beginning of this line to convert it
from a drop/create/alter to just a create/alter.
IF OBJECT_ID('dbo.spr_SprocTemplate') IS NOT NULL
DROP PROCEDURE dbo.spr_SprocTemplate
--*/

IF OBJECT_ID('dbo.spr_SprocTemplate') IS NULL
BEGIN
EXEC ('CREATE PROCEDURE dbo.spr_SprocTemplate AS SELECT 1')
EXEC ('GRANT EXECUTE ON dbo.spr_SprocTemplate TO db_sprocexecutor') --
custom role that has permissions to execute sprocs
END
GO

ALTER PROCEDURE dbo.spr_SprocTemplate
AS
BEGIN --Procedure
SET NOCOUNT ON

SELECT

END --Procedure

GO

GO
Hope that Helps!

-Eric Isaacs