StoredProcedureExists

Syntax

dbo.StoredProcedureExists( 'name' )

Arguments

name
sysname, required
The name of a procedure, optionally qualified with a schema name. When a schema name is specified, only procedures in that schema are searched.

Return Types

bit, not null
A value indicating whether a procedure with the specified name exists.

Usage Examples

Note that you can't use CREATE PROCEDURE or ALTER PROCEDURE inside a conditional block. You can use sp_executesql as a workaround. The following example uses sp_executesql to ensure that a placeholder procedure with the specified name exists. You can then use ALTER PROCEDURE on the placeholder procedure in a new batch.

-- Create a placeholder that can be altered later
IF dbo.StoredProcedureExists('dbo.GetSalesByProduct') = 0
    EXEC sp_executesql @statement = N'CREATE PROCEDURE dbo.GetSalesByProduct AS;'
GO

ALTER PROCEDURE dbo.GetSalesByProduct
AS -- Actual implementation
SELECT ...;

IF dbo.StoredProcedureExists('GetSalesByProduct') = 1
  DROP PROCEDURE GetSalesByProduct;