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

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

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.

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

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.

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