How to detect if a stored procedure already exists

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')GOALTER PROCEDURE dbo.YourSpAS...

This way, security settings, comments and other meta deta will survive the deployment.

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 NULLDROP PROC MySprocGOCREATE PROC MySprocASBEGIN    ...END

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