Stored Procedure Exceptions
Go Up to Working with Stored Procedures
An exception is a named error message that can be raised from a stored procedure. Exceptions are created with
CREATE EXCEPTION, modified with
ALTER EXCEPTION, and dropped with
DROP EXCEPTION. A stored procedure raises an exception with
When raised, an exception returns an error message to the calling program and terminates execution of the procedure that raised it, unless the exception is handled by a WHEN statement.
- Important: Like procedures, exceptions are created and stored in a database, where they can be used by any procedure that needs them. Exceptions must be created and committed before they can be raised.
For more information on raising and handling exceptions, see Raising an Exception in a Stored Procedure.
To create an exception, use the following
CREATE EXCEPTION syntax:
CREATE EXCEPTION name '<message>';
For example, the following statement creates an exception named
CREATE EXCEPTION REASSIGN_SALES 'Reassign the sales records before deleting this employee.';
To change the message returned by an exception, use the following syntax:
ALTER EXCEPTION name '<message>';
Only the creator of an exception can alter it. For example, the following statement changes the text of the exception created in the previous section:
ALTER EXCEPTION REASSIGN_SALES 'Can’t delete employee--Reassign Sales';
You can alter an exception even though a database object depends on it. If the exception is raised by a trigger, you cannot drop the exception unless you first drop the trigger or stored procedure. Use
ALTER EXCEPTION instead.
To delete an exception, use the following syntax:
DROP EXCEPTION name;
For example, the following statement drops the exception,
DROP EXCEPTION REASSIGN_SALES;
The following restrictions apply to dropping exceptions:
- Only the creator of an exception can drop it.
- Exceptions used in existing procedures and triggers cannot be dropped.
- Exceptions currently in use cannot be dropped.
- Tip: In isql,
SHOW PROCEDURESdisplays a list of dependencies, the procedures, exceptions, and tables which the stored procedure uses.
SHOW PROCEDURE<name> displays the body and header information for the named procedure.
SHOW TRIGGERS<table> displays the triggers defined for <table>.
SHOW TRIGGER<name> displays the body and header information for the named trigger.
Raising an Exception in a Stored Procedure
To raise an exception in a stored procedure, use the following syntax:
where <name> is the name of an exception that already exists in the database.
When an exception is raised, it does the following:
- Terminates the procedure in which it was raised and undoes any actions performed (directly or indirectly) by the procedure.
- Returns an error message to the calling application. In isql, the error message is displayed on the screen.
- Note: If an exception is handled with a
WHENstatement, it behaves differently. For more information on exception handling, see Handling Exceptions.
The following statements raise the exception,
<source="sql"> IF (any_sales > 0) THEN EXCEPTION REASSIGN_SALES; </source>