Categories:
UDF (User-Defined Function) & Stored Procedure DDL

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.

See also:
CREATE PROCEDURE , DROP PROCEDURE , SHOW PROCEDURES

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 Syntax.

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;