Nested compound statements and exception handlers

The code following a statement that causes an error is not executed unless an ON EXCEPTION RESUME clause is included 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.

Drop the procedures

Remember to drop both the InnerProc and OuterProc procedures by entering the following commands in the command window 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”

CREATE PROCEDURE InnerProc()
BEGIN
	DECLARE column_not_found
		EXCEPTION FOR SQLSTATE VALUE '52003';
		MESSAGE 'Hello from InnerProc';
BEGIN
		SIGNAL column_not_found;
			MESSAGE 'Line following SIGNAL'
	EXCEPTION
		WHEN column_not_found THEN
			MESSAGE 'Column not found handling';
		WHEN OTHERS THEN
			RESIGNAL;
END
		MESSAGE 'Outer compound statement';
END

The following statement executes the InnerProc procedure:

CALL InnerProc();

The message window of the server or the dbisql 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 handlingmessage is printed. Control then passes back to the outer compound statement and the Outer compound statementmessage is printed.

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.