Sometimes you may need to drop procedure in MySQL or delete procedure in MySQL as you don’t need it anymore. Here’s how to delete stored procedure in MySQL using MySQL DROP PROCEDURE statement. You can use it to drop all procedures in MySQL or drop one procedure in MySQL.
How To Delete Stored Procedure in MySQL
Here are the steps to delete stored procedure in MySQL using MySQL DROP PROCEDURE statement.
Here’s the syntax of MySQL DROP PROCEDURE statement
DROP PROCEDURE [IF EXISTS] stored_procedure_name;
In the above statement, you need to specify the name of stored procedure.
If you try to delete a stored procedure that does not exist, MySQL will give an error. So you can optionally use IF EXISTS keyword to ensure that stored procedure is deleted only if it exists.
Bonus Read : MySQL Change User Password
Let us say you have the following stored procedure sample_procedure as follows.
DELIMITER $$
CREATE PROCEDURE get_orders()
BEGIN
SELECT *
FROM orders;
END$$
Bonus Read : MySQL Cast as Boolean
Here’s the SQL statement to delete stored procedure in MySQL
mysql> DROP PROCEDURE get_orders(); Query OK, 0 rows affected (0.04 sec)
If you try to drop procedure that doesn’t exist, you will get the following error
mysql> drop procedure get_orders; ERROR 1305 (42000): PROCEDURE sample.get_orders does not exist
However, if you drop procedure using IF EXISTS keyword, you will only get a warning, not error.
mysql> drop procedure IF EXISTS get_orders; Query OK, 0 rows affected, 1 warning (0.00 sec)
Bonus Read : MySQL CAST vs BOOLEAN
How to Drop All Procedures in MySQL
MySQL allows you to drop only one procedure at a time. So if you want to drop all procedures, you will need to create separate DROP PROCEDURE statements. Here’s a SELECT statement that creates a list of drop procedure statements. Its result is a list of DROP PROCEDURE statements, one for each stored procedure in your database. However, use it with caution.
SELECT
CONCAT('DROP ',ROUTINE_TYPE,' `',ROUTINE_SCHEMA,'`.`',ROUTINE_NAME,'`;') as stmt
FROM information_schema.ROUTINES;
Hopefully, now you can easily delete procedure in MySQL.
Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
