Important

The SingleStore 9.1 release candidate (RC) gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 9.0 is recommended for production workloads, which can later be upgraded to SingleStore 9.1.

DROP PROCEDURE

Syntax

SQL

DROP PROCEDURE [IF EXISTS] { procedure_name | database_name.procedure_name } [WITH ID <variant ID>]

Remarks

Only one stored procedure can be removed using the DROP PROCEDURE command.
If DROP PROCEDURE is executed for a procedure that does not exist, the following error occurs:
SQL
```
DROP PROCEDURE myprocedure;
```
```
ERROR 1998 (HY000): Function 'db1.myprocedure' doesn't exist
```

However, if the IF EXISTS condition is used and the procedure does not exist, no error will occur:
SQL
```
DROP PROCEDURE IF EXISTS myprocedure;
```
```
Query OK, 0 rows affected (0.00 sec)
```

This command causes implicit commits. Refer to COMMIT for more information.
Refer to the Permission Matrix for the required permissions.
If a procedure is overloaded, DROP PROCEDURE requires the procedure's variant ID which is available from SHOW FUNCTIONS. Refer to Overloaded Functions and Stored Procedures for more information.

Examples

Drop a Procedure in the Current Database

The following example removes an existing procedure from the current database.

Refer to Overloaded Functions and Stored Procedures for an example of dropping an overloaded procedure.

SQL

SHOW FUNCTIONS;

+------------------+------------------+
| Functions_in_db1 | Function Type    |
+------------------+------------------+
| myprocedure      | Stored Procedure |
+------------------+------------------+
1 row in set (0.00 sec)

SQL

DROP PROCEDURE myprocedure;

SHOW FUNCTIONS;

Query OK, 0 rows affected (0.07 sec)

Dropping a Procedure in Another Database

The following example removes an existing stored procedure while connected to another database in your SingleStore cluster.

SQL

USE db1;

SHOW FUNCTIONS;

+------------------+------------------+
| Functions_in_db1 | Function Type    |
+------------------+------------------+
| myprocedure      | Stored Procedure |
+------------------+------------------+
1 row in set (0.00 sec)

SQL

USE db2;

DROP PROCEDURE db1.myprocedure;

USE db1;

SHOW FUNCTIONS;

Query OK, 0 rows affected (0.07 sec)

Related Topics