ASA SQL User's Guide
  Using Procedures, Triggers, and Batches
    Errors and warnings in procedures and triggers

Error handling with ON EXCEPTION RESUME

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:

• IF

• SELECT @variable =

• CASE

• LOOP

• LEAVE

• CONTINUE

• CALL

• EXECUTE

• SIGNAL

• RESIGNAL

• DECLARE

• SET VARIABLE

The following example illustrates how this works.

Drop the procedures 

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();
   SELECT @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:

CALL OuterProc();

The Interactive SQL 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 appears.