How to detect if a stored procedure already exists

Question

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.

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

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

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

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