Categories
sql sql-server stored-procedures tsql

How to check if a stored procedure exists before creating it

327

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?

8

  • 2

    no it doesn’t work, because that creates a stored procedure which is allegedly not what you want. from what we can see, it doesn’t drop it after its done, either, so it’s definitely stored in all aspects of the term. it is not irrelevant why you need a non-stored procedure

    Jan 15, 2010 at 14:18

  • What do you mean by ‘non-stored’ procedure? All your sample does is recreate a stored procedure; what does this have to do with your question?

    – AakashM

    Jan 15, 2010 at 14:19

  • Ok, there we go. The thing is, I have a HUGE SQL script which many clients use and has to be ran thoroughly every time a client executes the “database management” functionality that our software provides. So some of these clients might already have the procedure stored upon running the script, and some may not. I know this is stupid, I don’t actually need this procedure to remain unstored, I can just check if it exists and create it if it doesn’t. However, it doesn’t matter how much I try to bend T-SQL syntax, there’s always an error.

    Jan 15, 2010 at 14:34

  • Every time they run the script, it will try to create the procedure again (unfortunatelly, it everything has to be scripted in the same .sql file including the create procedure call). IF NOT EXISTS THEN CREATE doesn’t work due to syntax limitations. What can I do?

    Jan 15, 2010 at 14:48

  • 3

    Possible duplicate of stackoverflow.com/questions/937908/…

    May 21, 2013 at 2:11

233

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.

Update:

In SQL Server 2016+, you can just do

CREATE OR ALTER PROCEDURE dbo.MyProc

9

  • Yes this is true, but you will loose all procedural functionality as no procedures, udfs, views and such will be stored to call from within queries. (Sorry, edited it, it did make sense in my head X-))

    Jan 15, 2010 at 14:40


  • 1

    Yes, but you can call procedures from within other procedures, or use their return as input to a table.

    Jan 15, 2010 at 14:47

  • @astander: you can call anonymous code from the stored procedures as well. To use their output in an INSERT, you’ll need to use OPENROWSET or OPENQUERY which works with the anonymous code as well. Of course there are drawbacks in the anonymous code: for instance, it only runs under the caller’s privileges. My point is that it is possible, not preferred way of doing things 🙂

    – Quassnoi

    Jan 15, 2010 at 14:50

  • “If you only need to create a procedure if it not exists, then your code is just fine.” And that’s exactly what I wanted to know. I tried to use SSMS Create to on the actual script but it didn’t do any good. But thanks Quassnoi, and I’m sorry about the unclear question.

    Jan 15, 2010 at 15:00

  • 2

    A CREATE PROC statement must be the only statement in a batch when not using dynamic SQL so you cannot wrap a transaction around the DROP/CREATE when implemented in this manner. There has to be a GO (batch separator) after the DROP PROC call.

    – Shiv

    Jan 13, 2015 at 1:04


233

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.

Update:

In SQL Server 2016+, you can just do

CREATE OR ALTER PROCEDURE dbo.MyProc

9

  • Yes this is true, but you will loose all procedural functionality as no procedures, udfs, views and such will be stored to call from within queries. (Sorry, edited it, it did make sense in my head X-))

    Jan 15, 2010 at 14:40


  • 1

    Yes, but you can call procedures from within other procedures, or use their return as input to a table.

    Jan 15, 2010 at 14:47

  • @astander: you can call anonymous code from the stored procedures as well. To use their output in an INSERT, you’ll need to use OPENROWSET or OPENQUERY which works with the anonymous code as well. Of course there are drawbacks in the anonymous code: for instance, it only runs under the caller’s privileges. My point is that it is possible, not preferred way of doing things 🙂

    – Quassnoi

    Jan 15, 2010 at 14:50

  • “If you only need to create a procedure if it not exists, then your code is just fine.” And that’s exactly what I wanted to know. I tried to use SSMS Create to on the actual script but it didn’t do any good. But thanks Quassnoi, and I’m sorry about the unclear question.

    Jan 15, 2010 at 15:00

  • 2

    A CREATE PROC statement must be the only statement in a batch when not using dynamic SQL so you cannot wrap a transaction around the DROP/CREATE when implemented in this manner. There has to be a GO (batch separator) after the DROP PROC call.

    – Shiv

    Jan 13, 2015 at 1:04


137

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!

1

  • 70

    That better: IF (OBJECT_ID(‘MyProcedure’, ‘P’) IS NOT NULL) DROP PROCEDURE MyProcedure GO

    – alerya

    May 11, 2012 at 9:42