The WHENEVER Statement

Explicitly checking the value of SQLCODE or SQLSTATE after each embedded SQL statement can involve writing a lot of code. As an alternative, check the status of the SQL statement by using a WHENEVER statement in your application.

The WHENEVER statement is not an executable statement. It is a directive to the Compiler to automatically generate code that handles errors after each executable embedded SQL statement.

The WHENEVER statement allows one of three default actions (CONTINUE, GOTO or PERFORM) to be registered for each of the following conditions:

Condition Value of SQLCODE
NOT FOUND 100
SQLWARNING +1
SQLERROR < 0 (negative)

A WHENEVER statement for a particular condition replaces all previous WHENEVER statements for that condition.

The scope of a WHENEVER statement is related to its physical position in the source program, not its logical position in the run sequence. For example, in the following code if the first SELECT statement does not return anything, paragraph A is performed, not paragraph C:

     EXEC SQL
         WHENEVER NOT FOUND PERFORM A
     END-EXEC.
     perform B.
     EXEC SQL
        SELECT col1 into :host-var1 FROM table1 
         WHERE col2 = :host-var2
     END-EXEC.
 A.
     display "First item not found".
 B.
     EXEC SQL
         WHENEVER NOT FOUND PERFORM C.
     END-EXEC.
 C.
     display "Second item not found".

COBSQL

For Oracle, Sybase and Informix, setting SQLWARN0 to "W" triggers the SQLWARNING clause.

Oracle

When no data is returned from a SELECT or FETCH statement, the condition NOT FOUND is triggered, regardless of the setting of the Oracle precompiler directive MODE.

Informix

Informix allows you to perform a STOP or a CALL from within a WHENEVER statement. These are additions to the ANSI standard and are documented in the Informix ESQL/COBOL programmers manual.