How to check if a stored procedure exists before creating it

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?

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.

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

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!

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:

Drop the procedure (if it already exists) and then
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'

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

How to check if a stored procedure exists before creating it

Related

Recent Posts