ALTER PROCEDURE (Transact-SQL)

Last updated: 2023-10-27

Note: This topic describes the Transact-SQL syntax for the ALTER PROCEDURE statement. For information about using SQL Server Management Studio (SSMS) to modify stored procedures, see Modify a Stored Procedure.

Modifies a stored procedure. ALTER PROCEDURE has the same functionality as ALTER PROC.

Syntax

ALTER PROCEDURE [ schema_name.procedure_name ]
    [ < input_output_para > [ ,...n ] ]
    [ WITH [ < options > ] [ ,...n ] ]
    [ AS ]
    < < < transact_sql_statement > > >
GO

< input_output_para > ::=
    { parameter data_type
        [ = default ]
        [ OUTPUT ]
    }

< options > ::=
    {
          RECOMPILE
        | ENCRYPTION
        | RECOMPILE
        | < compiled_option > [ ,...n ]
    }

< < < transact_sql_statement > > > ::=
    { sql_batch }

Parameters

`schema_name.procedure_name`

The name of the stored procedure to modify. You can optionally specify the schema to which the procedure belongs.

`input_output_para`

Specifies the parameters and their data types for the stored procedure. The procedure can have input parameters, output parameters, or both. Parameters must be defined in the order they are passed.

parameter: The name of a parameter. Parameter names must start with an at sign (@).
data_type: The data type of the parameter.
= default: The default value for the parameter.
OUTPUT: Indicates that the parameter is an output parameter.

`WITH <options>`

Specifies options for the stored procedure:

RECOMPILE: Indicates that the procedure should be recompiled the next time it is executed. This can improve performance if the procedure's execution plan is no longer optimal.
ENCRYPTION: Encrypts the procedure's definition using the sys.sql_modules catalog view. This prevents the procedure's definition from being published as clear text in SQL Server system catalogs.

`AS`

A keyword that separates the procedure definition from the procedure body.

`transact_sql_statement`

The Transact-SQL statements that make up the body of the stored procedure. This can include declarative statements, control-of-flow statements, and data manipulation statements.

`GO`

A Transact-SQL statement terminator that indicates the end of a batch of Transact-SQL statements.

Permissions

Requires ALTER permission on the procedure.

Examples

Example 1: Changing procedure parameters

The following example modifies the uspUpdateEmployeeHireDate stored procedure to add a new output parameter @NewHireDate.

ALTER PROCEDURE uspUpdateEmployeeHireDate
    @EmpID int,
    @NewHireDate date,
    @ModifiedDate datetime = GETDATE() OUTPUT
AS
SET NOCOUNT ON;

    UPDATE HumanResources.Employee
    SET HireDate = @NewHireDate, ModifiedDate = @ModifiedDate
    WHERE EmployeeID = @EmpID;
GO

Example 2: Recompiling a procedure

The following example alters the usp_GetProductInfo procedure and specifies that it should be recompiled the next time it is executed.

ALTER PROCEDURE usp_GetProductInfo
    @ProductID int
    WITH RECOMPILE
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ProductID, Name, ListPrice
    FROM Production.Product
    WHERE ProductID = @ProductID;
END
GO