DROP PROCEDURE (SQL)
Synopsis
DROP PROCEDURE [IF EXISTS] procname [ FROM className ]
DROP PROC procname [ FROM className ]
Description
The DROP PROCEDURE command deletes a procedure in the current namespace. When you drop a procedure, InterSystems IRIS 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).
You cannot drop a procedure if the class definition that contains that procedure definition is a deployed class. This operation fails with an SQLCODE -400 error with the %msg Unable to execute DDL that modifies a deployed class: 'classname'.
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.Schema.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, InterSystems IRIS will also drop the methods related to the procedure, such as myprocExecute(), myprocGetInfo(), myprocFetch(), myprocFetchRows(), and myprocClose().
You can also delete a procedure by removing the stored procedure from the class definition and then recompiling the class, or by deleting the entire class.
Arguments
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 default schema name, unless the FROM className clause is specified.
FROM className
If specified, the FROM className clause deletes the procedure from the given class. If this clause is not specified, InterSystems IRIS 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.
Examples
The following example attempts to delete myprocSP from the class User.Employee. (Refer to CREATE TABLE for an example that creates class User.Employee.)
DROP PROCEDURE myprocSP FROM User.Employee
