How to Drop Stored Procedure in MySQL: Syntax and Examples

To drop a stored procedure in MySQL, use the DROP PROCEDURE procedure_name; statement. This command removes the stored procedure from the database permanently.

Syntax

The syntax to drop a stored procedure in MySQL is simple:

• DROP PROCEDURE procedure_name; - Removes the stored procedure named procedure_name.
• You must have the necessary privileges to drop the procedure.
• If the procedure does not exist, MySQL will return an error unless you use IF EXISTS.

sql

DROP PROCEDURE [IF EXISTS] procedure_name;

Example

This example shows how to drop a stored procedure named GetCustomerOrders. It first creates the procedure, then drops it.

sql

DELIMITER $$
CREATE PROCEDURE GetCustomerOrders()
BEGIN
  SELECT * FROM Orders WHERE CustomerID = 1;
END$$
DELIMITER ;

-- Now drop the procedure
DROP PROCEDURE IF EXISTS GetCustomerOrders;

Output

Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

Common Pitfalls

Common mistakes when dropping stored procedures include:

• Trying to drop a procedure that does not exist without using IF EXISTS, which causes an error.
• Not having sufficient privileges to drop the procedure.
• Confusing stored procedures with functions; dropping a function requires DROP FUNCTION.

sql

/* Wrong: causes error if procedure does not exist */
DROP PROCEDURE GetCustomerOrders;

/* Right: avoids error if procedure does not exist */
DROP PROCEDURE IF EXISTS GetCustomerOrders;

Quick Reference

Command	Description
DROP PROCEDURE procedure_name;	Drops the specified stored procedure.
DROP PROCEDURE IF EXISTS procedure_name;	Drops the procedure only if it exists, avoiding errors.
SHOW PROCEDURE STATUS;	Lists all stored procedures in the current MySQL server instance.
CREATE PROCEDURE procedure_name ...	Creates a new stored procedure.

Key Takeaways

Use DROP PROCEDURE procedure_name; to remove a stored procedure in MySQL.

Add IF EXISTS to avoid errors if the procedure does not exist.

Ensure you have the right privileges to drop procedures.

Dropping a function requires DROP FUNCTION, not DROP PROCEDURE.

Check existing procedures with SHOW PROCEDURE STATUS;