How to check if a stored procedure exists before creating it

How to Check if a Stored Procedure Exists Before Creating It in SQL Server

Learn robust methods to verify the existence of a SQL Server stored procedure, preventing errors and ensuring idempotent script execution.

When developing or deploying SQL Server scripts, it's common practice to create or alter stored procedures. However, directly attempting to CREATE PROCEDURE if one already exists will result in an error. To ensure your scripts are idempotent (meaning they can be run multiple times without causing errors or changing the result beyond the initial application), you need a reliable way to check for a stored procedure's existence before proceeding. This article explores several effective methods to achieve this, focusing on best practices and common scenarios.

Why Check for Stored Procedure Existence?

The primary reason to check for a stored procedure's existence is to prevent errors during script execution. If you try to CREATE PROCEDURE when a procedure with the same name already exists, SQL Server will throw an error. Conversely, if you try to ALTER PROCEDURE and the procedure doesn't exist, you'll also get an error. By implementing a check, you can gracefully handle both scenarios, either by creating the procedure if it's new or altering it if it already exists. This is crucial for deployment scripts, automated builds, and maintaining database consistency across environments.

flowchart TD
    A[Start Script Execution] --> B{Stored Procedure Exists?}
    B -->|Yes| C[Drop Existing Procedure]
    C --> D[Create New Procedure]
    B -->|No| D
    D --> E[End Script Execution]

Workflow for conditionally creating or altering a stored procedure.

Method 1: Using OBJECT_ID

The OBJECT_ID function is a highly efficient and widely recommended way to check for the existence of any schema-scoped object in SQL Server, including stored procedures. It returns the object identification number of a schema-scoped object. If the object does not exist, OBJECT_ID returns NULL.

IF OBJECT_ID('dbo.MyStoredProcedure', 'P') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.MyStoredProcedure;
END;
GO

CREATE PROCEDURE dbo.MyStoredProcedure
AS
BEGIN
    -- Your stored procedure logic here
    SELECT 'Hello from MyStoredProcedure';
END;
GO

Checking and dropping a stored procedure using OBJECT_ID before creation.

Method 2: Using sys.objects or sys.procedures

Another robust method involves querying the system catalog views, specifically sys.objects or sys.procedures. These views provide detailed metadata about all objects in the database. sys.objects contains all schema-scoped objects, while sys.procedures is a more specific view for just procedures.

IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.MyStoredProcedure') AND type = 'P')
BEGIN
    DROP PROCEDURE dbo.MyStoredProcedure;
END;
GO

CREATE PROCEDURE dbo.MyStoredProcedure
AS
BEGIN
    SELECT 'Hello from MyStoredProcedure (using sys.objects)';
END;
GO

Checking for a stored procedure using sys.objects.

IF EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'MyStoredProcedure' AND SCHEMA_NAME(schema_id) = 'dbo')
BEGIN
    DROP PROCEDURE dbo.MyStoredProcedure;
END;
GO

CREATE PROCEDURE dbo.MyStoredProcedure
AS
BEGIN
    SELECT 'Hello from MyStoredProcedure (using sys.procedures)';
END;
GO

Checking for a stored procedure using sys.procedures.

Best Practice: IF EXISTS (DROP) CREATE vs. ALTER

There are two common patterns for managing stored procedures: IF EXISTS (DROP) CREATE and CREATE OR ALTER. While CREATE OR ALTER is available in SQL Server 2016 (13.x) and later, the IF EXISTS (DROP) CREATE pattern is more universally compatible with older SQL Server versions and offers explicit control.

-- For SQL Server 2016 (13.x) and later
CREATE OR ALTER PROCEDURE dbo.MyStoredProcedure
AS
BEGIN
    SELECT 'Hello from MyStoredProcedure (CREATE OR ALTER)';
END;
GO

Using CREATE OR ALTER for newer SQL Server versions.