SQL Reference

WHENEVER

The WHENEVER statement specifies the action to be taken when a specified exception condition occurs.

Invocation

This statement can only be embedded in an application program. It is not an executable statement. The statement is not supported in REXX.

Authorization

None required.

Syntax

>>-WHENEVER----+-NOT FOUND--+----------------------------------->
               +-SQLERROR---+
               '-SQLWARNING-'
 
>-----+-CONTINUE--------------------------+--------------------><
      '--+-GOTO--+---+----+---host-label--'
         '-GO TO-'   '-:--'
 

Description

The NOT FOUND, SQLERROR, or SQLWARNING clause is used to identify the type of exception condition.

NOT FOUND

Identifies any condition that results in an SQLCODE of +100 or an SQLSTATE of '02000'.

SQLERROR

Identifies any condition that results in a negative SQLCODE.

SQLWARNING

Identifies any condition that results in a warning condition (SQLWARN0 is 'W'), or that results in a positive SQL return code other than +100.

The CONTINUE or GO TO clause is used to specify what is to happen when the identified type of exception condition exists.

CONTINUE

Causes the next sequential instruction of the source program to be executed.

GOTO  or  GO TO host-label

Causes control to pass to the statement identified by host-label. For host-label, substitute a single token, optionally preceded by a colon. The form of the token depends on the host language.

Notes

There are three types of WHENEVER statements:

• WHENEVER NOT FOUND
• WHENEVER SQLERROR
• WHENEVER SQLWARNING

Every executable SQL statement in a program is within the scope of one implicit or explicit WHENEVER statement of each type. The scope of a WHENEVER statement is related to the listing sequence of the statements in the program, not their execution sequence.

An SQL statement is within the scope of the last WHENEVER statement of each type that is specified before that SQL statement in the source program. If a WHENEVER statement of some type is not specified before an SQL statement, that SQL statement is within the scope of an implicit WHENEVER statement of that type in which CONTINUE is specified.

Example

In the following C example, if an error is produced, go to HANDLERR. If a warning code is produced, continue with the normal flow of the program. If no data is returned, go to ENDDATA.

   EXEC SQL WHENEVER SQLERROR GOTO HANDLERR;
   EXEC SQL WHENEVER SQLWARNING CONTINUE;
   EXEC SQL WHENEVER NOT FOUND GO TO ENDDATA;