DROP PROCEDURE statement

Use the DROP PROCEDURE statement to drop a user-defined procedure from the database. This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

Notes:

¹ HCL OneDB™-SPL language only
² See Specific Name


Element	Description	Restrictions	Syntax
`function`	Name of a procedure or SPL function to drop	Must exist (that is, be registered) in the database	Identifier
`owner`	Name of UDR owner	Must own the procedure or SPL function	Owner name
`parameter _type`	The data type of the parameter	The data type (or list of data types) must be the same types (and in the same order) as those specified when the procedure was created	Identifier; Data Type
`procedure`	Name of user-defined procedure to drop	Must exist (that is, be registered) in the database	Database Object Name

Usage

Dropping a user-defined procedure removes the text and executable version of the procedure from the database. You cannot drop an SPL procedure within the same SPL procedure.

You cannot use the DROP ROUTINE, DROP FUNCTION, or DROP PROCEDURE statement to drop a protected routine. For more information about protected routines, see the description of the sysprocedures system catalog table in the HCL OneDB Guide to SQL: Reference.

To use the DROP PROCEDURE statement, you must be the owner of the procedure and also hold the Resource privilege on the database, or have the DBA privilege. You must also hold the Usage privilege on the programming language in which the UDR is written. To drop an external user-defined procedure, see also Dropping an External Procedure.

If the function or procedure name is not unique within the database, you must specify enough parameter_type information to disambiguate the name. If the database server cannot resolve an ambiguous UDR name whose signature differs from that of another UDR only in an unnamed ROW type parameter, an error is returned. (This error cannot be anticipated by the database server when the ambiguous function or procedure is defined.)

If you do not know whether a UDR is a user-defined procedure or a user-defined function, you can use the DROP ROUTINE statement. For more information, see DROP ROUTINE statement.

For compatibility with earlier HCL OneDB versions, you can use this statement to drop an SPL function that CREATE PROCEDURE created. You can include parameter data types after the name of the procedure to identify the procedure:

DROP PROCEDURE compare(int, int);

If you use the specific name for the user-defined procedure, you must also use the keyword SPECIFIC, as in the following example:

DROP SPECIFIC PROCEDURE compare_point;

If you include the optional IF EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if no procedure of the specified name is registered in the current database.

Determine whether a procedure exists

Before you attempt to drop a user-defined procedure, you can check for its existence in the database by querying the system catalog. In the following example, the SELECT statement retrieves from the sysprodures table any routines whose identifier is MyProcedure:

SELECT * FROM sysprocedures WHERE procname = MyProcedure;

If this query returns a single row, then a UDR called MyProcedure is registered in the current database.

If this query returns no rows, you do not need to issue the DROP PROCEDURE statement, but you might wish to verify that the WHERE clause specified the correct name, and that you are connected to the correct database.

If the query returns more than one row, then the routine name MyProcedure is overloaded in the current database, and you need to examine the attributes of the MyProcedure routines to determine which of them, if any, need to be unregistered by the DROP PROCEDURE statement.