If the ON EXCEPTION RESUME clause appears in the CREATE PROCEDURE statement, the procedure checks the following statement when an error occurs. If the statement handles the error, then the procedure continues executing, resuming at the statement after the one causing the error. It does not return control to the calling environment when an error occurred.
The behavior for procedures that use ON EXCEPTION RESUME can be modified by the on_tsql_error option setting. For more information, see on_tsql_error option [compatibility].
Error-handling statements include the following:
SELECT @variable =
The following example illustrates how this works.
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 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.' TO CLIENT; CALL InnerProc(); SET res=SQLSTATE; IF res='52003' THEN MESSAGE 'SQLSTATE set to ', res, ' in OuterProc.' TO CLIENT; END IF END; CREATE PROCEDURE InnerProc() ON EXCEPTION RESUME BEGIN DECLARE column_not_found EXCEPTION FOR SQLSTATE '52003'; MESSAGE 'Hello from InnerProc.' TO CLIENT; SIGNAL column_not_found; MESSAGE 'SQLSTATE set to ', SQLSTATE, ' in InnerProc.' TO CLIENT; END;
The following statement executes the OuterProc procedure:
The Interactive SQL Messages tab then displays the following:
Hello from OuterProc.
Hello from InnerProc.
SQLSTATE set to 52003 in OuterProc.
The execution path is as follows:
OuterProc executes and calls InnerProc.
In InnerProc, the SIGNAL statement signals an error.
The MESSAGE statement is not an error-handling statement, so control is passed back to OuterProc and the message is not displayed.
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 appears.