Using exception handlers in procedures

It is often desirable to intercept certain types of errors and handle them within a procedure, rather than pass the error back to the calling environment. This is done through the use of an exception handler.

You define an exception handler with the EXCEPTION part of a compound statement (see “Using compound statements”). Whenever an error occurs in the compound statement, the exception handler executes. Unlike errors, warnings do not cause exception handling code to be executed. Exception handling code also executes if an error appears in a nested compound statement or in a procedure invoked anywhere within the compound statement.

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 demonstration procedures used to illustrate exception handling are based on those used in “Default error handling in procedures”. In this case, additional code handles the column not found error in the InnerProc procedure.

CREATE PROCEDURE OuterProc()
BEGIN
	MESSAGE 'Hello from OuterProc.';
	CALL InnerProc();
	MESSAGE 'SQLSTATE set to ',
		SQLSTATE,' in OuterProc.'
END
CREATE PROCEDURE InnerProc()
BEGIN
	DECLARE column_not_found
		EXCEPTION FOR SQLSTATE '52003';
	MESSAGE 'Hello from InnerProc.';
	SIGNAL column_not_found;
	MESSAGE 'Line following SIGNAL.';
	EXCEPTION
		WHEN column_not_found THEN
			MESSAGE 'Column not found handling.';
		WHEN OTHERS THEN
	RESIGNAL ;
END

The EXCEPTION statement declares the exception handler itself. The lines following the EXCEPTION statement do not execute unless an error occurs. Each WHEN clause specifies an exception name (declared with a DECLARE statement) and the statement or statements to be executed in the event of that exception. The WHEN OTHERS THEN clause specifies the statement(s) to be executed when the exception that occurred does not appear in the preceding WHEN clauses.

In this example, the statement RESIGNAL passes the exception on to a higher-level exception handler. RESIGNAL is the default action if WHEN OTHERS THEN is not specified in an exception handler.

The following statement executes the OuterProc procedure:

CALL OuterProc();

The message window of the server or the dbisql Messages pane then displays the following:

Hello from OuterProc.

Hello from InnerProc.

Column not found handling.

SQLSTATE set to 00000 in OuterProc.

Notes

The EXCEPTION statements execute, rather than the lines following the SIGNAL statement in InnerProc.
As the error encountered was a column not found error, the MESSAGE statement included to handle the error executes, and SQLSTATE resets to zero (indicating no errors).
After the exception handling code is executed, control is passed back to OuterProc, which proceeds as if no error was encountered.
You should not use ON EXCEPTION RESUME together with explicit exception handling. The exception handler code is not executed if ON EXCEPTION RESUME is included.
You should use explicit exception handling code after each statement that may potentially generate an exception whenever you use ON EXCEPTION RESUME. You gain flexibility in handling errors, but the cost is more code and a higher risk of bugs in your code.
If the error handling code for the column not found exception is simply a RESIGNAL statement, control is passed back to the OuterProc procedure with SQLSTATE still set at the value 52003. This is just as if there were no error handling code in InnerProc. Since there is no error handling code in OuterProc, the procedure fails.

Exception handling and atomic compound statements

When an exception is handled inside a compound statement, the compound statement completes without an active exception and the changes before the exception are not reversed. This is true even for atomic compound statements. If an error occurs within an atomic compound statement and is explicitly handled, some but not all of the statements in the atomic compound statement are executed.