sql - Creating a stored procedure if it does not already exist

Question

I want to check if a list of stored procedures exist. I want this all to be done in 1 script, one by one. So far I have this format:

USE [myDatabase]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_1')
BEGIN
CREATE PROCEDURE sp_1
AS
.................
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_2')
BEGIN
CREATE PROCEDURE sp_2
AS
.................
END
GO

and so on. However, I'm getting the following error:

Incorrect syntax near the keyword 'Procedure'.

Why isn't what I'm doing working correctly?

question from:https://stackoverflow.com/questions/22950165/creating-a-stored-procedure-if-it-does-not-already-exist

Answer 1

CREATE PROCEDURE must be the first statement in the batch. I usually do something like this:

IF EXISTS (
        SELECT type_desc, type
        FROM sys.procedures WITH(NOLOCK)
        WHERE NAME = 'myProc'
            AND type = 'P'
      )
     DROP PROCEDURE dbo.myProc
GO

CREATE PROC dbo.myProc

AS
....

    GO
    GRANT EXECUTE ON dbo.myProc TO MyUser 

(don't forget grant statements since they'll be lost if you recreate your proc)

One other thing to consider when you are deploying stored procedures is that a drop can succeed and a create fail. I always write my SQL scripts with a rollback in the event of a problem. Just make sure you don't accidentally delete the commit/rollback code at the end, otherwise your DBA might crane-kick you in the trachea :)

BEGIN TRAN 
IF EXISTS (
       SELECT type_desc, type
       FROM sys.procedures WITH(NOLOCK)
       WHERE NAME = 'myProc'
           AND type = 'P'
     )
DROP PROCEDURE myProc GO
CREATE PROCEDURE myProc
   
AS
   --proc logic here

GO
-- BEGIN DO NOT REMOVE THIS CODE (it commits or rolls back the stored procedure drop) 
    IF EXISTS(
               SELECT 1
               FROM sys.procedures WITH(NOLOCK)
               WHERE NAME = 'myProc'
                   AND type = 'P'
             )
        COMMIT TRAN
        ELSE
        ROLLBACK TRAN
-- END DO NOT REMOVE THIS CODE