Error handling with ON EXCEPTION RESUME

If the ON EXCEPTION RESUME clause is included in the CREATE PROCEDURE statement, the procedure checks the following statement when an error occurs. If the statement handles the error, then the procedure does not return control to the calling environment when an error occurs. Instead, it continues executing, resuming at the statement after the one causing the error.

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 procedures show what happens when an application calls the procedure OuterProc; and OuterProc in turn calls the procedure InnerProc, which then encounters an error. These demonstration procedures are based on those used earlier in this section:

CREATE PROCEDURE OuterProc()
ON EXCEPTION RESUME
BEGIN
	DECLARE res CHAR(5);
	MESSAGE 'Hello from OuterProc.';
	CALL InnerProc();
	SELECT @res=SQLSTATE;
	IF @res='52003' THEN 
		MESSAGE 'SQLSTATE set to ',
			res, ' in OuterProc.';
	END IF
END;


CREATE PROCEDURE InnerProc()
ON EXCEPTION RESUME
BEGIN
	DECLARE column_not_found 
		EXCEPTION FOR SQLSTATE '52003';
	MESSAGE 'Hello from InnerProc.';
	SIGNAL column_not_found;
	MESSAGE 'SQLSTATE set to ', 
	SQLSTATE, ' in InnerProc.';
END

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. 

SQLSTATE set to 52003 in OuterProc. 

The execution path is as follows:

1. OuterProc executes and calls InnerProc.

2. In InnerProc, the SIGNAL statement signals an error.

3. The MESSAGE statement is not an error-handling statement, so control is passed back to OuterProc and the message is not displayed.

4. In OuterProc, the statement following the error assigns the SQLSTATE value to the variable named res. This is an error-handling statement, and so execution continues and the OuterProc message is displayed.