CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Sean Smyth
Sean Smyth

Reputation: 1581

Creating a stored procedure if it does not already exist

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?

Upvotes: 55

Views: 119836

Answers (9)

Ben Thul
Ben Thul

Reputation: 32737

One idiom that I've been using lately that I like quite a lot is:

if exists (select 1 from sys.objects where object_id = object_id('dbo.yourProc'))
   set noexec on
go
create procedure dbo.yourProc as
begin
   select 1 as [not yet implemented]
end
go
set noexec off
alter procedure dbo.yourProc as
begin
   /*body of procedure here*/
end

Essentially, you're creating a stub if the procedure doesn't exist and then altering either the stub (if it was just created) or the pre-existing procedure. The nice thing about this is that you don't drop a pre-existing procedure which drops all the permissions as well. You can also cause issues with any application that happens to want it in that brief instant where it doesn't exist.

[Edit 2018-02-09] - In SQL 2016 SP1, create procedure and drop procedure got some syntactic sugar that helps with this kind of thing. Specifically, you can now do this:

create or alter dbo.yourProc as
go

drop procedure if exists dbo.yourProc;

Both provide idempotency in the intended statement (i.e. you can run it multiple times and the desired state is achieved). This is how I'd do it now (assuming you're on a version of SQL Server that supports it).

Upvotes: 50

M.Ali
M.Ali

Reputation: 69594

USE [myDatabase]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_1')
BEGIN
  DROP PROCEDURE sp_1
END
GO   --<-- Add a Batch Separator here



CREATE PROCEDURE sp_1
AS
.................
END
GO

In SQL Server 2017 and later versions you can use the "IF EXISTS" to drop a proc or even better you can use "CREATE OR ALTER PROCEDURE"

USE [myDatabase]
GO

DROP PROCEDURE IF EXISTS sp_1;
GO   --<-- Add a Batch Separator here



CREATE OR ALTER PROCEDURE sp_1
AS
BEGIN
  .................
END
GO

You can simply ignore the "DROP IF EXISTS" command and just use "CREATE OR ALTER"

Upvotes: 4

Code Magician
Code Magician

Reputation: 24032

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

Upvotes: 55

Iskuskov Alexander
Iskuskov Alexander

Reputation: 4375

Updated on Sep 2020

You can use CREATE OR ALTER statement (was added in SQL Server 2016 SP1):

The CREATE OR ALTER statement acts like a normal CREATE statement by creating the database object if the database object does not exist and works like a normal ALTER statement if the database object already exists.

Upvotes: 17

Ajay Dagade
Ajay Dagade

Reputation: 101

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetRailItems]') AND type in (N'P', N'PC'))
BEGIN 
execute ('
CREATE PROCEDURE [dbo].[spGetRailItems]  
AS  
BEGIN  

Declare @isLiftedBagsEnable bit=1;  
select @isLiftedBagsEnable=cast(DataValu as bit) from setups where scope =''Rail Setting'' and dataName = ''isLiftedBagsEnable'';

IF @isLiftedBagsEnable=1
BEGIN
    IF EXISTS (SELECT * FROM ITEMCONFIG)
    BEGIN
        SELECT [Item],[Desc] FROM ProcData WHERE Item IN (SELECT Item FROM ItemConfig) ORDER BY [Desc]
    END
    ELSE
    BEGIN
        SELECT [Item],[Desc] FROM ProcData ORDER BY [Desc]
    END
END
ELSE
BEGIN
    SELECT [Item],[Desc] FROM ProcData ORDER BY [Desc]
END

END

')
END

exec spGetRailItems;

Upvotes: 8

Niladri
Niladri

Reputation: 5962

Just in case if you are using SQL server 2016, then there is a shorter version to check if the proc exist and then drop and recreate it 

USE [DATABASENAME]
GO
DROP PROCEDURE IF EXISTS <proc name>
GO
CREATE PROCEDURE <proc name>
AS
-- your script here
END
GO
GRANT EXECUTE ON <proc name> TO <username>

Source : https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/

Upvotes: 5

Ron
Ron

Reputation: 917

I know that there's an accepted answer, but the answer does not address exactly what the original question asks, which is to CREATE the procedure if it does not exist. The following always works and has the benefit of not requiring dropping procedures which can be problematic if one is using sql authentication. 

USE [MyDataBase]
GO

IF OBJECT_ID('mySchema.myProc') IS NULL
EXEC('CREATE PROCEDURE mySchema.myProc AS SET NOCOUNT ON;')
GO

ALTER PROCEDURE mySchema.myProc
    @DeclaredParmsGoHere    DataType

AS 
   BEGIN
       DECLARE @AnyVariablesINeed    Their DataType
   SELECT myColumn FROM myTable WHERE myIndex = @IndexParm

Upvotes: 27

jdlcgarcia
jdlcgarcia

Reputation: 183

you can execute the following:

DROP PROCEDURE IF EXISTS name_of_procedure;
CREATE PROCEDURE name_of_procedure(....)

Upvotes: 0

KM.
KM.

Reputation: 103707

I like to use ALTER so I don't lose permissions and if you have a syntax error the old version still exists:

BEGIN TRY
    --if procedure does not exist, create a simple version that the ALTER will replace.  if it does exist, the BEGIN CATCH will eliminate any error message or batch stoppage
    EXEC ('CREATE PROCEDURE AAAAAAAA AS DECLARE @A varchar(100); SET @A=ISNULL(OBJECT_NAME(@@PROCID), ''unknown'')+'' was not created!''; RAISERROR(@A,16,1);return 9999')
END TRY BEGIN CATCH END CATCH
GO

ALTER PROCEDURE AAAAAAAA 
(
     @ParamsHere varchar(10)
)
AS
PRINT 'HERE IN '+(OBJECT_NAME(@@PROCID))
GO

Upvotes: 3

Related Questions