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

User · Answer

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]

User · Answer

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

User · Answer

I know you want to  quot alter a procedure if it exists and create it if it does not exist quot  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 TR   Trigger  You can get the full list of options via  SELECT name  FROM master  spt values WHERE type    O9T

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

CREATE Procedure IF NOT EXISTS  Your proc-name     BEGIN     END

User · Answer

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

User · Answer

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

[sql] How to check if a stored procedure exists before creating it

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to stored-procedures