MySQL ALTER PROCEDURE MySQL · Stored Procedure · ALTER

MySQL ALTER PROCEDURE

Master MySQL ALTER PROCEDURE — modifying characteristics (COMMENT, READS SQL DATA, SQL SECURITY DEFINER/INVOKER) of existing stored procedures. Covers SHOW CREATE PROCEDURE for inspection, the drop-and-recreate idiom for changing the body or parameters (since MySQL has no CREATE OR REPLACE PROCEDURE), and the MySQL Workbench GUI flow.

What ALTER PROCEDURE Does

The ALTER PROCEDURE statement modifies an existing stored procedure — but only its characteristics, not its body or parameter list. You can change the comment, the SQL data access mode, the security context, but you cannot change what the procedure actually does or what arguments it takes through ALTER.

⚠️ This is the major limitation: ALTER PROCEDURE does not let you modify the procedure's body or change its parameters. To do that, you need to drop the procedure and recreate it. There's no in-place "edit the SQL" form of ALTER for stored procedures in MySQL.

Syntax

ALTER PROCEDURE sp_name [characteristic ...];

Notes on the syntax:

No parentheses after the procedure name. Just sp_name, not sp_name().
One or more characteristics follow the name. They're applied as a set.
Characteristics not mentioned in the ALTER are left unchanged.

Characteristics You Can Change

Characteristic	What it controls
`COMMENT 'string'`	Free-text description, visible via `SHOW CREATE PROCEDURE`
`LANGUAGE SQL`	The implementation language. Currently MySQL only supports SQL — kept for ANSI/ISO compatibility
`CONTAINS SQL`	Procedure contains SQL but doesn't read or write tables (default)
`NO SQL`	Procedure contains no SQL statements
`READS SQL DATA`	Procedure reads tables (SELECTs) but doesn't modify them
`MODIFIES SQL DATA`	Procedure may run INSERT/UPDATE/DELETE
`SQL SECURITY DEFINER`	Procedure runs with the privileges of the user who created it (default)
`SQL SECURITY INVOKER`	Procedure runs with the privileges of the user who called it

⚠️ DETERMINISTIC / NOT DETERMINISTIC cannot be changed via ALTER PROCEDURE. Despite the docs implying otherwise, MySQL's parser rejects them in ALTER. Drop and recreate the procedure to change determinism.

Examples

Example 1 — Create a Procedure, Then Inspect It

Start with a simple procedure that returns all employees:

MySQL — Create the procedure

DROP PROCEDURE IF EXISTS emp;

DELIMITER //

CREATE PROCEDURE emp()
BEGIN
    SELECT * FROM employees;
END //

DELIMITER ;

SHOW CREATE PROCEDURE displays the full definition the way the server has stored it. Append \G instead of ; in the mysql CLI to format the output vertically — much more readable for wide rows:

MySQL — Inspect

SHOW CREATE PROCEDURE emp\G

OUTPUT

mysql — show create

mysql>SHOW CREATE PROCEDURE emp\G

*************************** 1. row ***************************

Procedure: emp

sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `emp`()

BEGIN

SELECT * FROM employees;

END

character_set_client: utf8mb4

collation_connection: utf8mb4_0900_ai_ci

Database Collation: utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

Notice the procedure was stored with DEFINER=`root`@`localhost`. That's the SQL SECURITY DEFINER default — when anyone calls emp, MySQL runs it with root's privileges, regardless of who's calling.

Example 2 — ALTER to Add a Comment

Add a description to the procedure with ALTER PROCEDURE:

MySQL — Alter

ALTER PROCEDURE emp COMMENT 'Get all employees';

SHOW CREATE PROCEDURE emp\G

OUTPUT

mysql — alter procedure

mysql>ALTER PROCEDURE emp COMMENT 'Get all employees';

Query OK, 0 rows affected (0.01 sec)

mysql>SHOW CREATE PROCEDURE emp\G

*************************** 1. row ***************************

Procedure: emp

sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `emp`()

COMMENT 'Get all employees'

BEGIN

SELECT * FROM employees;

END

1 row in set (0.00 sec)

The new comment now appears in the stored definition. The body itself is unchanged.

Example 3 — Multiple Characteristics in One ALTER

You can change several characteristics at once:

MySQL — Multi-characteristic ALTER

ALTER PROCEDURE emp
    READS SQL DATA
    SQL SECURITY INVOKER
    COMMENT 'Get all employees — runs with caller privileges';

OUTPUT

mysql — multi alter

mysql>ALTER PROCEDURE emp READS SQL DATA SQL SECURITY INVOKER COMMENT '...';

Query OK, 0 rows affected (0.01 sec)

The procedure now declares it only reads data, runs with the caller's privileges, and has a useful comment — all in one statement.

Changing the Body — The Drop-and-Recreate Idiom

To modify what a procedure does, you have to drop and recreate it. The standard pattern:

MySQL — Update procedure body

DROP PROCEDURE IF EXISTS emp;

DELIMITER $$

CREATE PROCEDURE emp(IN dept INT)
    READS SQL DATA
    COMMENT 'Get employees in a specific department'
BEGIN
    SELECT employee_id, first_name, last_name, salary
    FROM   employees
    WHERE  department_id = dept
    ORDER  BY salary DESC;
END$$

DELIMITER ;

CALL emp(6);     -- engineering department

OUTPUT

mysql — recreated emp

mysql>CALL emp(6);

+-------------+------------+-----------+---------+

+-------------+------------+-----------+---------+

| 103 | Alexander | Hunold | 9000.00 |

| 104 | Bruce | Ernst | 6000.00 |

| 105 | David | Austin | 4800.00 |

| 106 | Valli | Pataballa | 4800.00 |

| 107 | Diana | Lorentz | 4200.00 |

+-------------+------------+-----------+---------+

5 rows in set (0.00 sec)

💡 Other databases have CREATE OR REPLACE PROCEDURE — MySQL doesn't. PostgreSQL, Oracle, and SQL Server's recent versions let you replace a procedure atomically. MySQL 8.0 still requires the explicit drop-and-recreate dance. Keep the two statements next to each other in your script and you'll be fine.

Modifying via MySQL Workbench

If you'd rather edit through a GUI, MySQL Workbench gives you a graphical procedure editor. The flow:

In the schema browser, expand your database, then expand Stored Procedures.
Right-click the procedure you want to modify, choose Alter Stored Procedure...
Workbench opens a new tab with the procedure's source — edit anything you like, including the body and parameters.
Click Apply. Workbench shows you the SQL Script Review window: it generates a DROP PROCEDURE followed by a CREATE PROCEDURE statement with your edits.
Click Apply again to execute the script. Click Finish when it's done.

📌 Workbench's "Alter Stored Procedure" really does drop and recreate. You can see this in the script review: there's no magic — it's the same drop-and-recreate idiom, just automated. This means you'll briefly have a moment where the procedure doesn't exist; if another session calls it then, they'll get an error.

Best Practices

Back up before changing. Save the output of SHOW CREATE PROCEDURE before dropping anything — easy to restore if needed.
Use ALTER for metadata, drop-and-recreate for code. Don't try to work around the body limitation; just embrace the recreate pattern.
Keep procedure source in version control. The authoritative copy should live in your Git repo, not in the database. Database becomes a deployment target, not the source of truth.
Wrap deployment in a transaction when possible — though note DDL like CREATE PROCEDURE auto-commits, so this only helps for the surrounding non-DDL work.
Test in non-production first. The drop-and-recreate window briefly leaves the procedure missing; live applications will see errors during that gap. Schedule changes for low-traffic periods.
Update the COMMENT when you change behavior so the next person reading SHOW CREATE PROCEDURE isn't misled.

Conclusion

ALTER PROCEDURE name characteristic ... changes a procedure's characteristics — comment, SQL data access mode, security context — but not its body or parameters.
To change the body or parameters, drop and recreate the procedure. MySQL has no CREATE OR REPLACE PROCEDURE.
Use SHOW CREATE PROCEDURE name\G to inspect a procedure's full definition.
The most useful characteristics to ALTER: COMMENT, READS SQL DATA / MODIFIES SQL DATA, SQL SECURITY DEFINER/INVOKER.
MySQL Workbench's "Alter Stored Procedure" feature really runs DROP + CREATE under the hood; understand this if you're using it on a live system.