How to check if a stored procedure exists before c

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?

Answer 1

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!

Answer 2

Check IF Exist For Stored Procedure

IF EXISTS (SELECT * FROM sys.objects 
            WHERE object_id = OBJECT_ID
             (N'[Schema].[Procedure_Name]') AND type IN (N'P', N'PC'))
BEGIN
       DROP PROCEDURE [Schema].[Procedure_Name]
       Print('Proceudre dropped => [Schema].[Procedure_Name]')
END

Check IF Exist for Trigger , Function also by clicking below link http://www.gurujipoint.com/2017/05/check-if-exist-for-trigger-function-and.html

Answer 3

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

..........

Answer 4

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.

Answer 5

I know you want to "alter a procedure if it exists and only delete it if it does not exist" but I believe it's simpler to just always drop the procedure and then re-create it. Here's how to drop the procedure only if it already exists:

IF OBJECT_ID('MyProcedure', 'P') IS NOT NULL
    DROP PROCEDURE MyProcedure
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

You can get the full list of options via:

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

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