show you how to use the DROP PROCEDUREstatement to drop an existing stored procedure.
Summary: in this tutorial, you will learn how to use the MySQL DROP PROCEDUREstatement to drop a stored procedure.
Introduction to the MySQL DROP PROCEDURE statement
The DROP PROCEDURE statement deletes a stored procedure created by the CREATE PROCEDURE statement.
The following shows the basic syntax of the DROP PROCEDURE statement:
DROP PROCEDURE [IF EXISTS] sp_name;Code language: SQL (Structured Query Language) (sql)In this syntax:
- First, specify the name of the stored procedure (
sp_name) that you want to delete after theDROP PROCEDUREkeywords. - Second, use
IF EXISTSoption to conditionally drop the stored procedure if it exists.
When you drop a procedure that does not exist without using the IF EXISTS option, MySQL will issue an error. In this case, if you use the IF EXISTS option, MySQL will issue a warning instead.
Note that you need to have the ALTER ROUTINE privilege of the stored procedure in order to remove it.
MySQL DROP PROCEDURE examples
Let’s take some examples of using the DROP PROCEDURE statement.
1) Basic MySQL DROP PROCEDURE statement example
First, create a new stored procedure that returns employee and office information:
DELIMITER $$
CREATE PROCEDURE GetEmployees()
BEGIN
SELECT
firstName,
lastName,
city,
state,
country
FROM employees
INNER JOIN offices using (officeCode);
END$$
DELIMITER ;Code language: SQL (Structured Query Language) (sql)Second, use the DROP PROCEDURE to delete the GetEmployees() stored procedure:
DROP PROCEDURE GetEmployees;Code language: SQL (Structured Query Language) (sql)2) Using MySQL DROP PROCEDURE with the IF EXISTS option example
The following example drops a stored procedure that does not exist:
DROP PROCEDURE abc;Code language: SQL (Structured Query Language) (sql)MySQL issued the following error:
Error Code: 1305. PROCEDURE classicmodels.abc does not existCode language: SQL (Structured Query Language) (sql)This statement drops the same non-existing stored procedure, but with IF EXISTSoption:
DROP PROCEDURE IF EXISTS abc;Code language: SQL (Structured Query Language) (sql)This time MySQL issued a warning.
0 row(s) affected, 1 warning(s): 1305 PROCEDURE classicmodels.abc does not existCode language: SQL (Structured Query Language) (sql)The statement SHOW WARNINGS shows the warning:
SHOW WARNINGS;Code language: SQL (Structured Query Language) (sql)Here is the output:
Dropping a stored procedure using MySQL Workbench
The following statement creates a new stored procedure named GetPayments() that returns the customer and payment information:
DELIMITER $$
CREATE PROCEDURE GetPayments()
BEGIN
SELECT
customerName,
checkNumber,
paymentDate,
amount
FROM payments
INNER JOIN customers
using (customerNumber);
END$$
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)To drop the stored procedure using MySQL Workbench, you follow these steps:
First, right-click the name of the stored procedure that you want to remove and choose Drop Stored Procedure… option.
MySQL Workbench will display a confirmation window.
Second, click Review SQL to review the SQL statement that MySQL Workbench will apply to the database, or click Drop Now if you want to immediately remove the stored procedure.
Third, review the SQL code and click the Execute button to drop the stored procedure.
Summary
- Use the
DROP PROCEDUREstatement to remove a stored procedure. - Use the
IF EXISTSoption to drop the stored procedure if it exists.