Skip to main content

DROP PROCEDURE

Deletes a procedure.

Synopsis

DROP PROCEDURE procname [ FROM className ]
DROP PROC procname [ FROM className ]

Arguments

Argument Description
procname The name of the procedure to be deleted. The name is an identifier. Do not specify the procedure’s parameter parentheses. A name can be qualified (schema.name), or unqualified (name). An unqualified procedure name takes the system-wide default schema name, unless the FROM className clause is specified.
FROM className Optional — If specified, the FROM className clause deletes the procedure from the given class. If this clause is not specified, Caché searches all classes of the schema for the procedure, and deletes it. However, if no procedure of this name is found, or more than one procedure of this name is found, an error code is returned. If the deletion of the procedure results in an empty class, DROP PROCEDURE deletes the class as well.

Description

The DROP PROCEDURE command deletes a procedure in the current namespace. When you drop a procedure, Caché revokes it from all users and roles to whom it has been granted and removes it from the database.

In order to drop a procedure, you must have %DROP_PROCEDURE administrative privilege, as specified by the GRANT command. If you are attempting to delete a procedure for a class with a defined owner, you must be logged in as the owner of the class. Otherwise, the system generates an SQLCODE -99 error (Privilege Violation).

The procname is not case-sensitive. You must specify procname without parameter parentheses; specifying parameter parentheses results in an SQLCODE -25 error.

The following combinations of procname and FROM className are supported. Note that the FROM clause specifies the class package name and procedure name, not the SQL names. In these examples, the system-wide default schema name is SQLUser, which corresponds to the User class package:

  • DROP PROCEDURE BonusCalc FROM procBonusCalc: drops the procedure SQLUser.BonusCalc().

  • DROP PROCEDURE BonusCalc FROM User.procBonusCalc: drops the procedure SQLUser.BonusCalc().

  • DROP PROCEDURE Test.BonusCalc FROM procBonusCalc: drops the procedure SQLUser.BonusCalc().

  • DROP PROCEDURE BonusCalc FROM Employees.procBonusCalc: drops the procedure Employees.BonusCalc().

  • DROP PROCEDURE Test.BonusCalc FROM Employees.procBonusCalc: drops the procedure Employees.BonusCalc().

If the specified procedure does not exist, DROP PROCEDURE generates an SQLCODE -362 error. If the specified class does not exist, DROP PROCEDURE generates an SQLCODE -360 error. If the specified procedure could refer to two or more procedures, DROP PROCEDURE generates an SQLCODE -361 error; you must specify a className to resolve this ambiguity.

To determine if a specified procname exists in the current namespace, use the $SYSTEM.SQL.ProcedureExists()Opens in a new tab method. This method recognizes both procedures and methods defined with the PROCEDURE keyword. A method defined with the PROCEDURE keyword can be deleted using DROP PROCEDURE.

If you execute a DROP PROCEDURE for a procedure that is an ObjectScript class query procedure, Caché will also drop the methods related to the procedure, such as myprocExecute(), myprocGetInfo(), myprocFetch(), myprocFetchRows(), and myprocClose().

Examples

The following embedded SQL example attempts to delete myprocSP from the class User.Employee. (Refer to CREATE TABLE for an example that creates class User.Employee.)

   &sql(DROP PROCEDURE myprocSP FROM User.Employee)
  IF SQLCODE=0 {
    WRITE !,"Procedure deleted" }
  ELSEIF SQLCODE=-360 {
    WRITE !,"Nonexistent class: ",%msg }
  ELSEIF SQLCODE=-362 {
    WRITE !,"Nonexistent procedure: ",%msg }
  ELSE {WRITE !,"Unexpected Error code: ",SQLCODE}

See Also

FeedbackOpens in a new tab