Modifying Stored Procedures in SQL Server 2022

Business rules evolve over time, and stored procedures must often be updated to reflect new validation logic, additional data changes, or revised error handling. In SQL Server 2022, stored procedures are typically modified using SQL Server Management Studio (SSMS) and Transact-SQL.

Modern Approach to Modifying Procedures

Earlier tools such as Enterprise Manager have been replaced by SSMS. Today, the recommended approach is to modify procedures directly with T-SQL. SQL Server 2016 and later also support CREATE OR ALTER PROCEDURE, which simplifies deployment by avoiding separate create and alter scripts.

CREATE OR ALTER PROCEDURE dbo.usp_DeleteEmployee
    @EmployeeID int
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM dbo.Employees WHERE EmployeeID = @EmployeeID)
    BEGIN
        DELETE FROM dbo.Timesheets
        WHERE EmployeeID = @EmployeeID;

        DELETE FROM dbo.Employees
        WHERE EmployeeID = @EmployeeID;
    END
    ELSE
    BEGIN
        THROW 50001, 'Employee ID does not exist.', 1;
    END
END;

ALTER PROCEDURE Syntax (Current Practice)

When you need to explicitly modify an existing procedure, use ALTER PROCEDURE. The syntax is largely identical to CREATE PROCEDURE, but it preserves permissions and dependencies.

ALTER PROCEDURE procedure_name
(
    @parameter data_type [ = default ] [ OUTPUT ]
    , ...
)
[WITH RECOMPILE]
AS
BEGIN
    -- Transact-SQL statements
END;

Key Syntax Elements (Updated for SQL Server 2022)

1. procedure_name — The name of the stored procedure being modified. Schema qualification (for example, dbo.usp_Name) is strongly recommended.
2. Procedure numbering (;number) — Deprecated and no longer recommended. Modern SQL Server uses unique procedure names instead.
3. @parameter — Named input or output arguments used to pass values into or out of the procedure. Multiple parameters may be defined.
4. data_type — The SQL Server datatype of the parameter. Every parameter must declare a datatype.
5. VARYING — Obsolete. Cursor-related options are handled differently and should not be used in new development.
6. default — A default value assigned to a parameter when no explicit value is supplied by the caller.
7. OUTPUT — Indicates that the parameter returns a value to the calling program.
8. WITH RECOMPILE — Forces recompilation on each execution. Use sparingly, typically only to address parameter sniffing issues.
9. WITH ENCRYPTION — Obfuscates procedure text. Not a security feature and generally discouraged because it complicates maintenance.
10. RECOMPILE, ENCRYPTION — Combination supported but rarely justified in modern environments.
11. FOR REPLICATION — Reserved for replication scenarios. Rarely used outside specialized replication configurations.
12. sql_code — The Transact-SQL statements that implement the procedure’s logic. Multiple statements are typically enclosed within a BEGIN…END block.

ALTER vs. DROP and Re-CREATE

Although it is technically possible to drop and recreate a stored procedure, this approach removes all existing permissions and can break dependencies. Using ALTER PROCEDURE or CREATE OR ALTER preserves security settings and is the preferred method in SQL Server 2022.

Example: Modifying an Existing Procedure

The following example modifies a procedure to delete related rows from multiple tables and uses modern error handling with THROW instead of RAISERROR.

ALTER PROCEDURE dbo.usp_DeleteEmployee
    @EmployeeID int
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM dbo.Employees WHERE EmployeeID = @EmployeeID)
    BEGIN
        DELETE FROM dbo.Timesheets
        WHERE EmployeeID = @EmployeeID;

        DELETE FROM dbo.Employees
        WHERE EmployeeID = @EmployeeID;
    END
    ELSE
    BEGIN
        THROW 50002, 'Employee ID does not exist.', 1;
    END
END;

In the next lesson, you will learn how to safely remove stored procedures from a SQL Server database.