Hey folks, in this tutorial, we will learn how to remove a stored procedure from your database using the DROP PROCEDURE statement. We will start with the introduction and move towards syntax to remove a stored procedure, followed by examples. You will also learn good practices that you should follow when writing MySQL queries. Here we go!
Introduction and Prerequisites
MySQL provides us with the DROP PROCEDURE statement to remove the stored procedure from the MySQL server. In this tutorial, we will learn to delete the procedure in two ways; through MySQL CLI and MySQL workbench.
Before getting started with the syntax, you can read our detailed guide on introduction to MySQL stored procedure if you are new to this topic.
Syntax of MySQL DROP PROCEDURE Statement
The DROP PROCEDURE statement is used to delete the stored procedure from the MySQL database. Following is the syntax to delete a stored procedure-
DROP PROCEDURE [IF EXISTS] stored_procedure_name;Code language: SQL (Structured Query Language) (sql)Where,
- DROP PROCEDURE – It is a statement to delete the stored procedure.
- IF EXISTS – It avoids displaying an error if the stored procedure you are trying to delete does not exist. It is an optional parameter. However, it is good practice to write the IF EXISTS clause.
- stored_procedure_name – The name of the stored procedure created by the CREATE PROCEDURE statement.
As stated above, if you use the IF EXISTS clause, MySQL will check if the procedure exists before the delete operation. MySQL will delete the procedure only if the procedure exists, else it will skip the delete operation and won’t show any error. However, you will see a warning message on the screen.
In order to delete any stored procedure, you must possess the ALTER ROUTINE privilege on the database. For more information about MySQL privileges, you can read our guide from here.
MySQL DROP PROCEDURE Examples
Let’s create a procedure first before taking examples of the DROP PROCEDURE statement. Following is the simple stored procedure that will display the data of the table.
DELIMITER //
CREATE PROCEDURE getData()
BEGIN
SELECT * FROM accounts;
END
//Code language: SQL (Structured Query Language) (sql)
Our stored procedure is created successfully. Let’s see an example to delete it.
Example 1 – DROP PROCEDURE Without IF EXISTS Option
Let’s try to delete the stored procedure created above without the IF EXISTS option.
DROP PROCEDURE getData;Code language: SQL (Structured Query Language) (sql)
As you can see, the procedure was deleted successfully.
But what if we try to delete the same procedure again? Let’s see.
DROP PROCEDURE getData;Code language: SQL (Structured Query Language) (sql)
You can see in the above snapshot, we get an error that the procedure does not exist.
To avoid such errors, we should use the IF EXISTS option.
Example 2 – DROP PROCEDURE With IF EXISTS Option
Let’s try to delete the stored procedure created above. It is already deleted but we will see what will be the output when the IF EXISTS option is used.
DROP PROCEDURE IF EXISTS getData;Code language: SQL (Structured Query Language) (sql)
As you can see, when we use the IF EXISTS option, we don’t get any errors. However, MySQL will raise a warning. You can see the warning by using the SHOW WARNINGS statement as shown in the image above. The warning is the same as the previous error but the query executes successfully unlike the previous statement where the query doesn’t execute successfully.
Example 3- Delete Stored Procedure using MySQL Workbench
It is the simplest way to delete a stored procedure from the database. Follow the below steps to delete a stored procedure-
- Open MySQL workbench and make a connection with the server.
- On the left-hand side, you will see all the databases.
- Click on the database and you will see the “stored procedures” option. Click on that.
- Now, all the procedures that reside in your database are listed.
- Right-click on the stored procedure that you want to delete.
- You will see the “drop stored procedure” option as shown in the image below. Click on it.
- You will get a new popup on the screen for confirmation. You can delete the procedure without reviewing it or after reviewing it.
- Your stored procedure is deleted successfully.
Summary
In this tutorial, we learned to:
- Use the DROP PROCEDURE statement.
- Delete a stored procedure without the IF EXISTS option.
- Delete a stored procedure with the IF EXISTS option.
- Delete a stored procedure using MySQL workbench.