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

Answer 1

EDIT: not putting text before code blocks kills it... always forget that :)

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

Answer 2

I agree with Luke but 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.

Answer 3

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

Answer 4

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') IS NOT NULL
DROP PROC MySproc
GO

CREATE PROC MySproc
AS
BEGIN
    ...
END

Answer 5

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

Answer 6

If you DROP and CREATE the procedure, you will loose the security settings. This might annoy your DBA or break your applicastion 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.

Answer 7

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.