Managing Stored Procedures

In addition to creating and calling stored procedures, you need to know how to view, modify, and drop them. MySQL provides several commands for managing the lifecycle of procedures.

Viewing Procedures

SHOW PROCEDURE STATUS — lists all procedures with metadata
SHOW CREATE PROCEDURE name — shows the full CREATE statement
SELECT from information_schema.ROUTINES — query procedure details programmatically

Modifying Procedures

MySQL does not support ALTER PROCEDURE for changing the body. To update a procedure, you must DROP it and recreate it. Use DROP PROCEDURE IF EXISTS to avoid errors when the procedure might not exist.

Permissions

Grant users the EXECUTE privilege to allow them to call a procedure without needing direct access to the underlying tables. This is a security best practice for exposing database operations to application users.

Example

-- List all procedures in current database
SHOW PROCEDURE STATUS WHERE Db = DATABASE();

-- Show procedure definition
SHOW CREATE PROCEDURE GetCustomerOrders;

-- Drop and recreate to modify
DROP PROCEDURE IF EXISTS GetCustomerOrders;

-- Grant execute permission
GRANT EXECUTE ON PROCEDURE GetCustomerOrders TO 'app_user'@'localhost';

SQL

Full Editor

Pro Tip

Always use DROP PROCEDURE IF EXISTS before recreating a procedure to avoid "already exists" errors.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned