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

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.