MySQL DROP PROCEDURE

 MySQL DROP PROCEDURE

Summary: in this tutorial, you will learn how to use the MySQL DROP PROCEDURE statement to drop a stored procedure.

Introduction to the MySQL DROP PROCEDURE statement

The DROP PROCEDURE deletes a stored procedure from the database.

The following shows the syntax of the DROP PROCEDURE statement:

DROP PROCEDURE [IF EXISTS] stored_procedure_name;

In this syntax:

• First, specify the name of the stored procedure that you want to remove after the DROP PROCEDURE keywords.
• Second, use IF EXISTS option to conditionally drop the stored procedure if it exists only.

When you drop a procedure that does not exist without using the IF EXISTS option, MySQL issues an error. In this case, if you use the IF EXISTS option, MySQL issues a warning instead.

Note that you must have the ALTER ROUTINE privilege for the stored procedure to remove it.

MySQL DROP PROCEDURE examples

Let’s take some examples of using the DROP PROCEDURE statement.

1) Using MySQL DROP PROCEDURE 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 ;

Second, use the DROP PROCEDURE to delete the GetEmployees() stored procedure:

DROP PROCEDURE GetEmployees;

2) Using MySQL DROP PROCEDURE with IF EXISTS example

The following example drops a stored procedure that does not exist:

DROP PROCEDURE abc;

MySQL issued the following error:

Error Code: 1305. PROCEDURE classicmodels.abc does not exist

This statement drops the same non-existing stored procedure, but with IF EXISTS option:

DROP PROCEDURE IF EXISTS abc;

This time, MySQL issued a warning.

0 row(s) affected, 1 warning(s): 1305 PROCEDURE classicmodels.abc does not exist

The statement SHOW WARNINGS shows the warning:

SHOW WARNINGS;

Here is the output:

Dropping a stored procedure using MySQL Workbench

This statement creates a new stored procedure named GetPayments():

DELIMITER $$

CREATE PROCEDURE GetPayments()
BEGIN
    SELECT 
        customerName, 
        checkNumber, 
        paymentDate, 
        amount
    FROM payments
    INNER JOIN customers 
        using (customerNumber);
END$$

DELIMITER ;

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 the 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 Drop Now if you want to remove the stored procedure immediately.

Third, review the SQL code to be executed and click the Execute button to drop the stored procedure.

In this tutorial, you have learned how to use the MySQL DROP PROCEDURE statement to drop a stored procedure.