How To Delete Stored Procedure in MySQL

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!

Sreeram Sreenivasan

Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.