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

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

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

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.

IF OBJECT_ID('SPNAME') IS NULL
     -- Does Not Exists
ELSE
     -- Exists

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

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

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.

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

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.

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 

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

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