How to detect if a stored procedure already exists

Question

I have to write a deployment script which will work if a stored procedure exists or does not exist    i e  if it exists  then I need to alter it  otherwise create it   How can I do this in the sql   I am using SQL Server 2005

User · Answer

You can write a query as follows   IF OBJECT ID  ProcedureName   P   IS NOT NULL     DROP PROC ProcedureName GO  CREATE PROCEDURE  dbo   ProcedureName     your query here       To be more specific on the above syntax  OBJECT ID is a unique id number for an object within the database  this is used internally by SQL Server  Since we are passing ProcedureName followed by you object type P which tells the SQL Server that you should find the object called ProcedureName which is of type procedure i e   P   This query will find the procedure and if it is available it will drop it and create new one   For detailed information about OBJECT ID and Object types please visit   SYS Objects

User · Answer

From SQL Server 2016 CTP3 you can use new DIE  statements instead of big IF wrappers  Syntax      DROP   PROC   PROCEDURE     IF EXISTS       schema name    procedure           n     Query   DROP PROCEDURE IF EXISTS usp name   More info here

User · Answer

If you are dealing only with stored procedures  the easiest thing to do is to probably drop the proc  then recreate it   You can generate all of the code to do this using the Generate Scripts wizard in SQL Server     IF  EXISTS  SELECT   FROM sys objects WHERE object id   OBJECT ID N  dbo   YourSproc    AND type in  N P   N PC    DROP PROCEDURE  dbo   YourSproc   CREATE PROCEDURE YourSproc

User · Answer

If you DROP and CREATE the procedure  you will loose the security settings   This might annoy your DBA or break your application altogether   What I do is create a trivial stored procedure if it doesn t exist yet   After that  you can ALTER the stored procedure to your liking   IF object id  YourSp   IS NULL     EXEC   create procedure dbo YourSp as select 1   GO ALTER PROCEDURE dbo YourSp AS       This way  security settings  comments and other meta deta will survive the deployment

User · Answer

if not exists  select   from dbo sysobjects where id   object id N  dbo   xxx    and OBJECTPROPERTY id  N IsProcedure     1  BEGIN CREATE PROCEDURE dbo xxx   where xxx is the proc name

User · Answer

The code below will check whether the stored procedure already exists or not    If it exists it will alter  if it doesn t exist it will create a new stored procedure for you     syntax for Create and Alter Proc  DECLARE  Create NVARCHAR 200     Create PROCEDURE sp cp test    DECLARE  Alter NVARCHAR 200    Alter PROCEDURE sp cp test      Actual Procedure  DECLARE  Proc NVARCHAR 200     AS BEGIN select   sh   END      Checking For Sp IF EXISTS  SELECT               FROM   sysobjects             WHERE  id   Object id   dbo   sp cp test                       AND Objectproperty id   IsProcedure     1                    AND xtype    p                     AND NAME    sp cp test      BEGIN        SET  Proc  Alter    Proc         EXEC   proc     END  ELSE    BEGIN        SET  Proc  Create    Proc         EXEC   proc     END   go

User · Answer

IF OBJECT ID  SPNAME   IS NULL      -- Does Not Exists ELSE      -- Exists

User · Answer

A better option might be to use a tool like Red-Gate SQL Compare or SQL Examiner to automatically compare the differences and generate a migration script

User · Answer

I have a stored proc that allows the customer to extend validation  if it exists I do not want to change it  if it doesn t I want to create it  the best way I have found    IF OBJECT ID  ValidateRequestPost   IS NULL BEGIN     EXEC   CREATE PROCEDURE ValidateRequestPost       RequestNo VARCHAR 30        ErrorStates VARCHAR 255  OUTPUT AS BEGIN     SELECT  ErrorStates    ErrorStates END   END

User · Answer

The cleanest way is to test for it s existence  drop it if it exists  and then recreate it   You can t embed a  create proc  statement inside an IF statement   This should do nicely   IF OBJECT ID  MySproc    P   IS NOT NULL DROP PROC MySproc GO  CREATE PROC MySproc AS BEGIN         END

User · Answer

In addition to what has already been said I also like to add a different approach and advocate the use of differential script deployment strategy  Instead of making a stateful script that always checks the current state and acts based on that state  deploy via a series of stateless scripts that upgrade from well known versions  I have used this strategy and it pays off big time as my deployment scripts are now all  IF  free

[sql] How to detect if a stored procedure already exists

Examples related to sql

Examples related to sql-server-2005

Examples related to stored-procedures