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?

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