MySQL - DROP PROCEDURE Statement

---

---

MySQL DROP PROCEDURE Statement

Stored procedures are sub routines, segment of SQL statements which are stored in SQL catalog. All the applications that can access Relational databases (Java, Python, PHP etc.), can access stored procedures.

Stored procedures contain IN and OUT parameters or both. They may return result sets in case you use SELECT statements. Stored procedures can return multiple result sets.

You can DELETE a function using the DROP PROCEDURE statement.

Syntax

Following is the syntax the DROP PROCEDURE statement −

DROP PROCEDURE procedure_name

Where, procedure_name is the name of the procedure you need to delete.

Example

Suppose we have created a table named Emp in the database as shown below −

CREATE TABLE Employee(
   Name VARCHAR(255), 
   Salary INT NOT NULL, 
   Location VARCHAR(255)
);

Assume we have created a stored procedure myProcedure which accepts the name, salary and location values and inserts them as a record into the above create table.

DELIMITER //
Create procedure myProcedure (
   IN name VARCHAR(30),
   IN sal INT,
   IN loc VARCHAR(45))
   BEGIN
      INSERT INTO Employee(Name, Salary, Location) VALUES (name, sal, loc);
   END //
DELIMITER ;

In the same way following procedure retrieves all the records of in the above created table −

DELIMITER //
Create procedure retrieveRecords ()
   BEGIN
      SELECT * FROM Dispatches;
   END //

You can verify the list of procedures in a database using the SHOW PROCEDURE STATUS statement as shown below −

SHOW PROCEDURE STATUS WHERE db = 'test'\G;

Output

The above mysql query will generate the following output −

************** 1. row **************
                  Db: test
                Name: myProcedure
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2023-12-05 15:19:39
             Created: 2023-12-05 15:19:39
       Security_type: DEFINER
             Comment:
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
************** 2. row **************
                  Db: test
                Name: retrieveRecords
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2023-12-05 15:20:18
             Created: 2023-12-05 15:20:18
       Security_type: DEFINER
             Comment:
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci

Following queries deletes/drops the above created procedures −

DROP PROCEDURE myProcedure;

DROP PROCEDURE retrieveRecords;

Verification

Since we have deleted both the procedures. If you verify the list of procedures again you will get an empty set −

SHOW PROCEDURE STATUS WHERE db = 'test';
Empty set (0.00 sec)

The IF EXISTS clause

If you try to drop a procedure that doesn’t exist, an error will be generated as shown below −

DROP Procedure demo;
ERROR 1305 (42000): FUNCTION test.demo does not exist

If you use the IF EXISTS clause along with the DROP PROCEDURE statement as shown below, the specified procedure will be dropped and if a procedure with the given name, doesn’t exist the query will be ignored.

DROP PROCEDURE IF EXISTS demo;