Errors and warnings in procedures and triggers

User's Guide
PART 1. Working with Databases
CHAPTER 10. Using Procedures, Triggers, and Batches

Errors and warnings in procedures and triggers

After an application program executes a SQL statement, it can examine a return code. This return code indicates whether the statement executed successfully or failed and gives the reason for the failure. The same mechanism can be used to indicate the success or failure of a CALL statement to a procedure.

Error reporting uses either the SQLCODE or SQLSTATE status descriptions. For full descriptions of SQLCODE and SQLSTATE error and warning values and their meanings, see Database Error Messages. Whenever a SQL statement is executed, a value is placed in special procedure variables called SQLSTATE and SQLCODE. That value indicates whether or not there were any unusual conditions encountered while the statement was being performed. You can check the value of SQLSTATE or SQLCODE in an IF statement following a SQL statement, and take actions depending on whether the statement succeeded or failed.

For example, the SQLSTATE variable can be used to indicate if a row is successfully fetched. The TopCustomerValue procedure presented in section Using cursors on SELECT statements in procedures used the SQLSTATE test to detect that all rows of a SELECT statement had been processed.

For Info Possible values for the SQLSTATE and SQLCODE variables are listed in Database Error Messages.

Default error handling in procedures and triggers

This section describes how Adaptive Server Anywhere handles errors that occur during a procedure execution, if you have no error handling built in to the procedure.

For Info If you want to have different behavior from that described in this section, you can use exception handlers, described in Using exception handlers in procedures and triggers. Warnings are handled in a slightly different manner from errors: for a description, see Default handling of warnings in procedures and triggers.

There are two ways of handling errors without using explicit error handling:

Default error handling The procedure or trigger fails and returns an error code to the calling environment.
ON EXCEPTION RESUME If the ON EXCEPTION RESUME clause is included in the CREATE PROCEDURE statement, the procedure carries on executing after an error, resuming at the statement following the one causing the error.

Default error handling

Generally, if a SQL statement in a procedure or trigger fails, the procedure or trigger terminates execution and control is returned to the application program with an appropriate setting for the SQLSTATE and SQLCODE values. This is true even if the error occurred in a procedure or trigger invoked directly or indirectly from the first one. In the case of a trigger, the operation causing the trigger is also undone and the error is returned to the application.

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.

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 'SQLSTATE set to ',
      SQLSTATE, ' in InnerProc.';
END

Notes

The DECLARE statement in InnerProc declares a symbolic name for one of the predefined SQLSTATE values associated with error conditions already known to the server. The DECLARE statement does not take any other action.
The MESSAGE statement sends a message to the server window and the dbconsol message window.
The SIGNAL statement generates an error condition from within the InnerProc procedure.

The following statement executes the OuterProc procedure:

CALL OuterProc();

The message window of the server then displays the following:

Hello from OuterProc.

Hello from InnerProc.

No statements following the SIGNAL statement in InnerProc are executed: InnerProc immediately passes control back to the calling environment, which in this case is the procedure OuterProc. No statements following the CALL statement in OuterProc are executed. The error condition is returned to the calling environment to be handled there. For example, Interactive SQL handles the error by displaying a message window describing the error.

The TRACEBACK function provides a list of the statements that were executing when the error occurred. You can use the TRACEBACK function from Interactive SQL by typing the following statement:

SELECT TRACEBACK(*)

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.

The following statements are considered error-handling statements:

IF
SELECT @variable =
CASE
LOOP
LEAVE
CONTINUE
CALL
EXECUTE
SIGNAL
RESIGNAL
DECLARE

The following example illustrates how this works.

Drop the procedures

Remember to drop both the InnerProc and OuterProc procedures before continuing with the tutorial. You can do this by entering the following commands in the command window:

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 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 is displayed.

Default handling of warnings in procedures and triggers

Warnings are handled differently from errors. While the default action for errors is to set a value for the SQLSTATE and SQLCODE variables, and return control to the calling environment, the default action for warnings is to set the SQLSTATE and SQLCODE values and continue execution of the procedure.

Drop the procedures

Remember to drop both the InnerProc and OuterProc procedures before continuing with the tutorial. You can do this by entering the following commands in the command window:

DROP PROCEDURE OUTERPROC;
DROP PROCEDURE INNERPROC

The following demonstration procedures illustrate default handling of warnings. These demonstration procedures are based on those used in Default error handling in procedures and triggers. In this case, the SIGNAL statement generates a row not found condition, which is a warning rather than an error.

CREATE PROCEDURE OuterProc()
BEGIN
   MESSAGE 'Hello from OuterProc.';
   CALL InnerProc();
   MESSAGE 'SQLSTATE set to ',
      SQLSTATE,' in OuterProc.';
END
CREATE PROCEDURE InnerProc()
BEGIN
   DECLARE row_not_found
      EXCEPTION FOR SQLSTATE '02000';
   MESSAGE 'Hello from InnerProc.';
   SIGNAL row_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 then displays the following:

Hello from OuterProc.

Hello from InnerProc.

SQLSTATE set to 02000 in InnerProc.

SQLSTATE set to 02000 in OuterProc.

The procedures both continued executing after the warning was generated, with SQLSTATE set by the warning (02000).

Using exception handlers in procedures and triggers

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

An exception handler is defined with the EXCEPTION part of a compound statement (see Using compound statements). The exception handler is executed whenever an error occurs in the compound statement. Unlike errors, warnings do not cause exception handling code to be executed. Exception handling code is also executed if an error is encountered in a nested compound statement or in a procedure or trigger that has been invoked anywhere within the compound statement.

Drop the procedures

Remember to drop both the InnerProc and OuterProc procedures before continuing with the tutorial. You can do this by entering the following commands in the command window:

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 and triggers. In this case, additional code is added to handle 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 are not executed 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 is not 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 then displays the following:

Hello from OuterProc.

Hello from InnerProc.

Column not found handling.

SQLSTATE set to 00000 in OuterProc.

Notes

The lines following the SIGNAL statement in InnerProc are not executed; instead, the EXCEPTION statements are executed.
As the error encountered was a column not found error, the MESSAGE statement included to handle the error is executed, and SQLSTATE is reset 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 handling code is not executed if ON EXCEPTION RESUME is included.
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. As 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 undone. 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.

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 are and are not executed following an error.

Drop the procedures

Remember to drop both the InnerProc and OuterProc procedures before continuing with the tutorial. You can do this by entering the following commands in the command window:

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 and triggers.

CREATE PROCEDURE InnerProc()
BEGIN
   DECLARE column_not_found
      EXCEPTION FOR SQLSTATE VALUE '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;
      MESSAGE 'Outer compound statement';
END

The following statement executes the InnerProc procedure:

CALL InnerProc();

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