How to check if a stored procedure exists before creating it

0 votes
asked Jan 15, 2010 by the-shaper

I have a SQL script that has to be run every time a client executes the "database management" functionality. The script includes creating stored procedures on the client database. Some of these clients might already have the stored procedure upon running the script, and some may not. I need to have the missing stored procedures added to the client database, but it doesn't matter how much I try to bend T-SQL syntax, I get

CREATE/ALTER PROCEDURE' must be the first statement in a query batch

I've read that dropping before creating works, but I don't like doing it that way.

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'MyProc')
DROP PROCEDURE MyProc
GO

CREATE PROCEDURE MyProc
...

How can I add check for the existence of a stored procedure and create it if it doesn't exist but alter it if it does exist?

19 Answers

0 votes
answered Jan 15, 2010 by no-refunds-no-return

This is the standard way of creating a data base when you're adding new elements and don't want to disrupt the existing entities.

0 votes
answered Jan 15, 2010 by gserg

If you need to modify a stored procedure before each launch, then maybe you can store its text as mere text in a varchar(max) column and execute it using sp_executesql.

Although I can't see why ALTER PROCEDURE would be worse than that.

0 votes
answered Jan 15, 2010 by quassnoi

You can run procedural code anywhere you are able to run a query.

Just copy everything after AS:

BEGIN
    DECLARE @myvar INT
    SELECT  *
    FROM    mytable
    WHERE   @myvar ...
END

This code does exactly same things a stored proc would do, but is not stored on the database side.

That's much like what is called anonymous procedure in PL/SQL.

Update:

Your question title is a little bit confusing.

If you only need to create a procedure if it not exists, then your code is just fine.

Here's what SSMS outputs in the create script:

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'myproc')
                    AND type IN ( N'P', N'PC' ) ) 
DROP …
CREATE …

Update:

Example of how to do it when including the schema:

IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'[dbo].[MyProc]') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE [dbo].[MyProc]
END

In the example above, dbo is the schema.

0 votes
answered Jan 21, 2010 by geoff

I realize this has already been marked as answered, but we used to do it like this:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND OBJECT_ID = OBJECT_ID('dbo.MyProc'))
   exec('CREATE PROCEDURE [dbo].[MyProc] AS BEGIN SET NOCOUNT ON; END')
GO

ALTER PROCEDURE [dbo].[MyProc] 
AS
  ....

Just to avoid dropping the procedure.

0 votes
answered Jan 16, 2011 by mrchips

If you're looking for the simplest way to check for a database object's existence before removing it, here's one way (example uses a SPROC, just like your example above but could be modified for tables, indexes, etc...):

IF (OBJECT_ID('MyProcedure') IS NOT NULL)
  DROP PROCEDURE MyProcedure
GO

This is quick and elegant, but you need to make sure you have unique object names across all object types since it does not take that into account.

I Hope this helps!

0 votes
answered Jan 17, 2011 by james

I apparently don't have the reputation required to vote or comment, but I just wanted to say that Geoff's answer using EXEC (sp_executesql might be better) is definitely the way to go. Dropping and then re-creating the stored procedure gets the job done in the end, but there is a moment in time where the stored procedure doesn't exist at all, and that can be very bad, especially if this is something that will be run repeatedly. I was having all sorts of problems with my application because a background thread was doing an IF EXISTS DROP...CREATE at the same time another thread was trying to use the stored procedure.

0 votes
answered Jan 15, 2012 by oaxas

I had the same error. I know this thread is pretty much dead already but I want to set another option besides "anonymous procedure".

I solved it like this:

  1. Check if the stored procedure exist:

    IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='my_procedure') BEGIN
        print 'exists'  -- or watever you want
    END ELSE BEGIN
        print 'doesn''texists'   -- or watever you want
    END
    
  2. However the "CREATE/ALTER PROCEDURE' must be the first statement in a query batch" is still there. I solved it like this:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE -- view procedure function or anything you want ...
    
  3. I end up with this code:

    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('my_procedure'))
    BEGIN
        DROP PROCEDURE my_procedure
    END
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE [dbo].my_procedure ...
    
0 votes
answered Jan 8, 2013 by dnxit

why don't you go the simple way like

    IF EXISTS(SELECT * FROM sys.procedures WHERE NAME LIKE 'uspBlackListGetAll')
    BEGIN
         DROP PROCEDURE uspBlackListGetAll
    END
    GO

    CREATE Procedure uspBlackListGetAll

..........

0 votes
answered Jan 21, 2014 by shiv

Here's a method and some reasoning behind using it this way. It isn't as pretty to edit the stored proc but there are pros and cons...

UPDATE: You can also wrap this entire call in a TRANSACTION. Including many stored procedures in a single transaction which can all commit or all rollback. Another advantage of wrapping in a transaction is the stored procedure always exists for other SQL connections as long as they do not use the READ UNCOMMITTED transaction isolation level!

1) To avoid alters just as a process decision. Our processes are to always IF EXISTS DROP THEN CREATE. If you do the same pattern of assuming the new PROC is the desired proc, catering for alters is a bit harder because you would have an IF EXISTS ALTER ELSE CREATE.

2) You have to put CREATE/ALTER as the first call in a batch so you can't wrap a sequence of procedure updates in a transaction outside dynamic SQL. Basically if you want to run a whole stack of procedure updates or roll them all back without restoring a DB backup, this is a way to do everything in a single batch.

IF NOT EXISTS (select ss.name as SchemaName, sp.name as StoredProc 
    from sys.procedures sp
    join sys.schemas ss on sp.schema_id = ss.schema_id
    where ss.name = 'dbo' and sp.name = 'MyStoredProc')
BEGIN
    DECLARE @sql NVARCHAR(MAX)

    -- Not so aesthetically pleasing part. The actual proc definition is stored
    -- in our variable and then executed.
    SELECT @sql = 'CREATE PROCEDURE [dbo].[MyStoredProc]
(
@MyParam int
)
AS
SELECT @MyParam'
    EXEC sp_executesql @sql
END
0 votes
answered Jan 30, 2014 by wartari

CREATE Procedure IF NOT EXISTS 'Your proc-name' () BEGIN ... END

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...