Categories:

DDL for User-Defined Functions, External Functions, and Stored Procedures

# ALTER PROCEDURE¶

Modifies the properties for an existing stored procedure. Currently, the only supported operations are renaming a stored procedure or adding/overwriting/removing a comment for a stored procedure. If you need to make any other changes to a stored procedure, use DROP PROCEDURE instead and then recreate the stored procedure.

## Syntax¶

ALTER PROCEDURE [ IF EXISTS ] <name> ( [ <arg_data_type> , ... ] ) RENAME TO <new_name>

ALTER PROCEDURE [ IF EXISTS ] <name> ( [ <arg_data_type> , ... ] ) SET COMMENT = '<string_literal>'

ALTER PROCEDURE [ IF EXISTS ] <name> ( [ <arg_data_type> , ... ] ) UNSET COMMENT

ALTER PROCEDURE [ IF EXISTS ] <name> ( [ <arg_data_type> , ... ] ) EXECUTE AS { CALLER | OWNER }


## Parameters¶

name

Specifies the identifier for the stored procedure to alter. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

arg_data_type [ , ... ]

Specifies the data type of the argument(s) for the stored procedure, if it has arguments. The argument types are required because stored procedures support name overloading (i.e. two stored procedures in the same schema can have the same name) and the argument types are used to identify the procedure you wish to alter.

RENAME TO new_name

Specifies the new identifier for the stored procedure; the combination of the identifier and existing argument data types must be unique for the schema.

For more details, see Identifier Requirements.

When an object is renamed, other objects that reference it must be updated with the new name.

SET ...

COMMENT = 'string_literal'

Adds a comment or overwrites the existing comment for the stored procedure. The value you specify is displayed in the DESCRIPTION column in the output for SHOW PROCEDURES.

UNSET ...

Specifies the properties to unset for the stored procedure, which resets them to the defaults.

Currently, the only property you can unset is COMMENT, which removes the comment, if any, for the procedure.

EXECUTE AS

A stored procedure can execute with the privileges of the owner (an “owner’s rights” stored procedure) or with the privileges of the caller (a “caller’s rights” stored procedure). If you execute the statement ALTER PROCEDURE ... EXECUTE AS CALLER, then when the procedure is called in the future, it will execute as a caller’s rights procedure. If you execute the statement ALTER PROCEDURE ... EXECUTE AS OWNER, then the in the future the procedure will execute as an owner’s rights procedure.

## Examples¶

Rename stored procedure procedure1 to procedure2:

ALTER PROCEDURE IF EXISTS procedure1(FLOAT) RENAME TO procedure2;