ALTER PROCEDURE

This MySQL statement changes the characteristics of an existing stored procedure.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

ALTER PROCEDURE stored_procedure
  [{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}]
  [SQL SECURITY {DEFINER|INVOKER}]
  [COMMENT 'string']

Explanation

This statement changes the characteristics of an existing stored procedure. You cannot change the procedure itself with it. To do that, you need to delete the procedure with DROP PROCEDURE and create a new procedure with CREATE PROCEDURE. See the description of that statement for an explanation of each characteristic.

There are three types of characteristics that you can set or change with this statement: the types of interaction with the server, the user recognized for SQL security, and a comment. Each type may be given in a space-separated list, in any order. See CREATE PROCEDURE for a discussion of the characteristics. The COMMENT clause replaces any existing comment. To clear a comment without inserting another, give two quotes with nothing between them.

This statement requires the CREATE ROUTINE privilege. The ALTER ROUTINE and EXECUTE privileges are granted to the user and account that creates or alters a stored procedure, by default.

Examples

An example of this statement follows:

ALTER PROCEDURE students_copy_proc
SQL SECURITY INVOKER
COMMENT 'Copies data from students table to students_backup.
Add a comment with @ref_note.'

If you look at the example for CREATE PROCEDURE, you'll see that the example here is changing the procedure created in that example. We're only adding that the user account to be used for executing the procedure will be the invoker, and we're adding a comment about the procedure—we didn't include one when we created the procedure.