How to check if a stored procedure exists before creating it

I realize this has already been marked as answered, but we used to do it like this:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND OBJECT_ID = OBJECT_ID('dbo.MyProc'))   exec('CREATE PROCEDURE [dbo].[MyProc] AS BEGIN SET NOCOUNT ON; END')GOALTER PROCEDURE [dbo].[MyProc] AS  ....

Just to avoid dropping the procedure.

You can run procedural code anywhere you are able to run a query.

Just copy everything after AS:

BEGIN    DECLARE @myvar INT    SELECT  *    FROM    mytable    WHERE   @myvar ...END

This code does exactly same things a stored proc would do, but is not stored on the database side.

That's much like what is called anonymous procedure in PL/SQL.

Update:

Your question title is a little bit confusing.

If you only need to create a procedure if it not exists, then your code is just fine.

Here's what SSMS outputs in the create script:

IF EXISTS ( SELECT  *            FROM    sys.objects            WHERE   object_id = OBJECT_ID(N'myproc')                    AND type IN ( N'P', N'PC' ) ) DROP …CREATE …

Update:

Example of how to do it when including the schema:

IF EXISTS ( SELECT *             FROM   sysobjects             WHERE  id = object_id(N'[dbo].[MyProc]')                    and OBJECTPROPERTY(id, N'IsProcedure') = 1 )BEGIN    DROP PROCEDURE [dbo].[MyProc]END

In the example above, dbo is the schema.

Update:

In SQL Server 2016+, you can just do

CREATE OR ALTER PROCEDURE dbo.MyProc

If you're looking for the simplest way to check for a database object's existence before removing it, here's one way (example uses a SPROC, just like your example above but could be modified for tables, indexes, etc...):

IF (OBJECT_ID('MyProcedure') IS NOT NULL)  DROP PROCEDURE MyProcedureGO

This is quick and elegant, but you need to make sure you have unique object names across all object types since it does not take that into account.

I Hope this helps!