Microsoft Docs

Overview

This article explains how to modify an existing stored procedure in Microsoft SQL Server. You can change the procedure’s definition, alter its parameters, or update its logic without dropping and recreating it.

Syntax

ALTER PROCEDURE [ schema_name . ] procedure_name 
    [ { @parameter_name [type_schema_name.] data_type 
        [ = default ] 
        [ OUT | OUTPUT ] } [ ,...n ] ]
[ WITH  [ ,...n ] ]
AS 
    { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

Parameters

• schema_name – Optional schema qualifier. Default is dbo.
• procedure_name – Name of the stored procedure to modify.
• @parameter_name – Existing parameter you wish to modify or new parameters to add.
• data_type – SQL Server data type for the parameter.
• default – Optional default value for the parameter.
• OUT | OUTPUT – Indicates an output parameter.
• procedure_option – Options such as ENCRYPTION, RECOMPILE, or EXECUTE AS clause.

Example: Adding a Parameter

The following example adds a new optional parameter @IsActive to the existing procedure dbo.GetCustomerOrders and updates the query accordingly.

ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerID INT,
    @StartDate  DATE = NULL,
    @EndDate    DATE = NULL,
    @IsActive   BIT  = 1      -- New parameter
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderID, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerID = @CustomerID
      AND ( @StartDate IS NULL OR OrderDate >= @StartDate )
      AND ( @EndDate   IS NULL OR OrderDate <= @EndDate )
      AND IsActive = @IsActive;
END;

Remarks

• When altering a procedure, ensure that dependent applications are aware of any signature changes.
• Use WITH ENCRYPTION to hide the definition, but note it can complicate maintenance.
• Running sp_refreshsqlmodule after modifications updates metadata for dependent objects.

See Also

• Creating Stored Procedures
• Executing Stored Procedures
• Deleting Stored Procedures
• Procedure Parameters