ASA SQL User's Guide
Using Procedures, Triggers, and Batches
Errors and warnings in procedures and triggers
The code following a statement that causes an error executes only if an ON EXCEPTION RESUME clause appears in a procedure definition.
You can use nested compound statements to give you more control over which statements execute following an error and which do not.
Remember to drop both the InnerProc and OuterProc procedures by entering the following commands in the SQL Statements pane before continuing with the tutorial:
DROP PROCEDURE OuterProc; DROP PROCEDURE InnerProc
The following demonstration procedure illustrates how nested compound statements can be used to control flow. The procedure is based on that used as an example in Default error handling in procedures and triggers.
CREATE PROCEDURE InnerProc() BEGIN BEGIN DECLARE column_not_found EXCEPTION FOR SQLSTATE VALUE '52003'; MESSAGE 'Hello from InnerProc' TO CLIENT; SIGNAL column_not_found; MESSAGE 'Line following SIGNAL' TO CLIENT EXCEPTION WHEN column_not_found THEN MESSAGE 'Column not found handling' TO CLIENT; WHEN OTHERS THEN RESIGNAL; END; MESSAGE 'Outer compound statement' TO CLIENT; END
The following statement executes the InnerProc procedure:
CALL InnerProc();
The Interactive SQL Messages pane then displays the following:
Hello from InnerProc Column not found handling Outer compound statement
When the SIGNAL statement that causes the error is encountered, control passes to the exception handler for the compound statement, and the Column not found handling
message prints. Control then passes back to the outer compound statement and the Outer compound statement
message prints.
If an error other than column not found
is encountered in the inner compound statement, the exception handler executes the RESIGNAL statement. The RESIGNAL statement passes control directly back to the calling environment, and the remainder of the outer compound statement is not executed.