How to check if a stored procedure exists before creating it

Question

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?

Adventurer

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 – Heartstricken

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? – Gametophyte

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. – Adventurer

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? – Adventurer

Possible duplicate of #938408 – Boloney

Right click on Stored Proc in ObjectExplorer -> Script -> Drop & Create ( Geoff has syntax ) – Lille

Also asked at #22950665 - with at least one other suggestion (using SET NOEXEC ON if the SP exists) – Woodworker

If the object has a replication subscription, it will error on a simple DROP PROC call. Doing the negative check gets around this by checking to see if it does not exist, and if so creates a shell. Either way an ALTER is to follow. – Hillock

Answer 1

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

Cleodel

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-)) – Broth

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

@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 :) – Cleodel

"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. – Adventurer

Note it's possible to have the same Procedure Name in different schema's so you should check the schema name too. See @Shiv's answer below for an example of how to do this. – Tinea

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. – Desta

I know it's an old question, but instead of SELECT * better use SELECT 1, you don't need to select the whole row just to check if it exists. – Melnick

@Milana: it makes no difference, the optimizer is smart enough to figure it out. – Cleodel

The "CREATE OR ALTER PROCEDURE" made my life so much simpler - thank you!! – Loganiaceous

Answer 2

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.

Streptomycin

Just to add some notes about why this is a good idea: 1) a drop will clear any security settings, 2) by doing it this way, if the alter script fails for some reason, the sp will not have been dropped. – Cathleencathlene

@SergioRomero Well, I can't really test it thoroughly right now, but according to msdn.microsoft.com/en-us/library/ms190324.aspx the type value in sys.objects for UDFs is "U = Table (user-defined)", so I would start there – Streptomycin

One can also query against sys.procedures, which shows only objects of type P, X, RF, and PC. (It's probably all the same internally.) – Betwixt

There is a huge benefit to this approach in that there is no point in time when the stored procedure does not exist. This can be crucial if the update is being applied to a critical system while it is still in use by other people, systems, or threads. Tracking down the errors caused by momentarily dropping a stored procedure can be quite vexing because they are very difficult to reproduce. – Geek

This is a great solution for many reasons already mentioned, and I'd just like to add that, in case the DBAs rely on proc meta-data (such as created-date), this leaves that stuff intact, instead of making the proc brand-new every time. I'm trying to get this turned into my team's "best practice" for maintaining our own procs, which typically have to be copied/propagated to several DBs. – Eng

Is there also a dummy create statement for a Trigger? – Oligarch

Also consider that some people want the GRANT statements explicity in the script in case they change; so there is still justification to use DROP instead of ALTER. – Eucalyptus

Why is SET NOCOUNT ON a good choice for the temporary stored procedure body? Asked here: #40849874 – Halflength

@Streptomycin I know it has been a while for this answer but could you elaborate on how I can include things such as comments in the procedure - author , purpose etc. I also have a function I intend to do like this, but some parts have a /* and I would like to know how to do that properly. – Tuner

My preference for the temp sproc body has been to throw a "not implemented" error or warning that makes it clear the stub is in place and rather than letting it silent succeed while not doing what it is mean to do. – Extraterritorial

I see one problem with this, but it deppends on your project needs. If you want to run this ONLY for create and not always ALTER, because you will lose any modifications you have done on your proc, if you run this too often, you will never be able to have a different implementation of this proc other than the one in this script, if this script is inside an application, hard coded. You will have to recompile the application after each and all modifications to the procedure. – Cottonmouth

Answer 3

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.

Patras

That better: IF (OBJECT_ID('MyProcedure', 'P') IS NOT NULL) DROP PROCEDURE MyProcedure GO – Gael

Answer 4

I know you want to "ALTER a procedure if it exists and create it if it does not exist", but I believe it is simpler to:

1. Drop the procedure (if it already exists) and then
2. Re-create it.

Like this:

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

CREATE PROCEDURE MyProcedure AS
BEGIN
    /* ..... */
END
GO

The second parameter tells OBJECT_ID to only look for objects with object_type = 'P', which are stored procedures:

AF = Aggregate function (CLR)

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

FN = SQL scalar function

FS = Assembly (CLR) scalar-function

FT = Assembly (CLR) table-valued function

IF = SQL inline table-valued function

IT = Internal table

P = SQL Stored Procedure

PC = Assembly (CLR) stored-procedure

PG = Plan guide

PK = PRIMARY KEY constraint

R = Rule (old-style, stand-alone)

RF = Replication-filter-procedure

S = System base table

SN = Synonym

SO = Sequence object

TF = SQL table-valued-function

TR = Trigger

You can get the full list of options via:

SELECT name 
FROM master..spt_values
WHERE type = 'O9T'

Inchoate

TF is missing. Still, +1 for providing this list – Fellowman

Also TR for Trigger – Literature

"and only delete it if it does not exist". No need to delete it if it doesn't exist. ;-) Change to "create it". Couldn't edit it myself. – Arlinda

Answer 5

As of SQL SERVER 2016 you can use the new DROP PROCEDURE IF EXISTS.
DROP { PROC | PROCEDURE } [ IF EXISTS ] { [ schema_name. ] procedure } [ ,...n ]

Reference : https://msdn.microsoft.com/en-us/library/ms174969.aspx

Wald

Answer 6

I know it is a very old post, but since this appears in the top search results hence adding the latest update for those using SQL Server 2016 SP1 -

create or alter procedure procTest
as
begin
 print (1)
end;
go

This creates a Stored Procedure if does not already exist, but alters it if exists.

Reference

Premeditation

I want to emphasis this only works in SQL Studio - in a sql file it fails for me. – Blimey

Answer 7

DROP IF EXISTS is a new feature of SQL Server 2016

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

DROP  PROCEDURE IF EXISTS dbo.[procname]

Siva

this is not SqlServer syntax..., advice to remove answer before guys start downvoting and to avoid confusion for newbies. – Propound

@PawelCz it is valid for SQL Server 2016 and up, I reworded the answer. Thanks for the feedback! – Siva

This doesn't answer the original post. There is a subtle difference between automagically dropping and recreating and only creating if it doesn't exist. Dropping a proc will drop security associated with it, which may have been scripted. – Pyrrhuloxia

@Pyrrhuloxia Not only security but also associated extended properties have to be recreatead. Object_id is changed too https://mcmap.net/q/94178/-what-do-you-do-in-sql-server-to-create-or-alter – Damato

Answer 8

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 ...
   

Bozcaada

You don't need the begin and end if it's only 1 line of code like DROP PROCEDURE... – Kolosick

Warning: the 'check if the stored procedure exists' function will always return 'exists', no matter what function name you put in (for T-SQL). It is an unreliable check. – Degeneration

A better alternative: IF EXISTS(SELECT 1 FROM sys.procedures WHERE name = 'name_of_table_as_seen_in_sysprocedures') BEGIN select -1 as 'status' END – Degeneration

Answer 9

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

Desta

Answer 10

In Sql server 2008 onwards, you can use "INFORMATION_SCHEMA.ROUTINES"

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES 
  WHERE ROUTINE_NAME = 'MySP'
        AND ROUTINE_TYPE = 'PROCEDURE') 

Hydrometallurgy

Answer 11

**The simplest way to drop and recreate a stored proc in T-Sql is **

Use DatabaseName
go
If Object_Id('schema.storedprocname') is not null
begin
   drop procedure schema.storedprocname
end
go

create procedure schema.storedprocname
as

begin
end

Stans

Answer 12

Here is the script that I use. With it, I avoid unnecessarily dropping and recreating the stored procs.

IF NOT EXISTS (
    SELECT *
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[uspMyProcedure]')
    )
BEGIN
  EXEC sp_executesql N'CREATE PROCEDURE [dbo].[uspMyProcedure] AS select 1'
END
GO

ALTER PROCEDURE [dbo].[uspMyProcedure] 
    @variable1 INTEGER  
AS
BEGIN
   -- Stored procedure logic
END

Mapel

Answer 13

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

..........

Codie

Bad idea to use a LIKE % statement here. What if the OP had another sproc such as uspBlackListGetAll_V2 that they didn't want dropping? – Machellemachete

@DaveHogan I agree. However he didn't put a %, so the LIKE behaves as an = – Lysin

@DiegoJancic if you look at the edited history you'll see it was originally with a '%' – Machellemachete

Answer 14

In addition to the answer from @Geoff I've created a simple tool which generates a SQL-file which statements for Stored Procedures, Views, Functions and Triggers.

See MyDbUtils @ CodePlex.

Oligarch

I think Management Studio already gives such tool. It's called "Generate scripts" – Wald

Answer 15

I wonder! Why i don't write the whole query like

GO
create procedure [dbo].[spAddNewClass] @ClassName varchar(20),@ClassFee int
as
begin
insert into tblClass values (@ClassName,@ClassFee)
end

GO
create procedure [dbo].[spAddNewSection] @SectionName varchar(20),@ClassID       int
as
begin
insert into tblSection values(@SectionName,@ClassID)
end

Go
create procedure test
as
begin 
select * from tblstudent
end

i already know that first two procedures are already exist sql will run the query will give the error of first two procedures but still it will create the last procedure SQl is itself taking care of what is already exist this is what i always do to all my clients!

Lagena

Answer 16

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

Revenuer

this would do nothing if the procedure exists. The requester wants to alter the procedure if it exists, create it if not. – Luis