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. It must not be specified in Java™ or REXX. See the Embedded SQL Programming topic collection for information about handling errors in REXX.

Authorization

None required.

Syntax

Read syntax diagramSkip visual syntax diagramWHENEVERNOT FOUNDSQLERRORSQLWARNINGCONTINUEGOTOGO TO:host-labelDOhost-procedure()EXSRsubroutinePERFORMhost-label

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 SQLSTATE of '02000' or an SQLCODE of +100.
SQLERROR
Identifies any condition that results in a negative SQLCODE'.
SQLWARNING
Identifies any condition that results in an SQLSTATE value where the first two characters are '01' or a warning condition (SQLWARN0 is 'W'), or that results in a positive SQLCODE other than +100.

The CONTINUE, GO TO, or DO clauses are used to specify the next statement to be executed when the identified type of exception condition exists.

CONTINUE
Specifies the next sequential instruction of the source program.
GOTO or GO TO host-label
Specifies 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. In a COBOL program, for example, it can be a section-name or an unqualified paragraph-name.
Start of changeDO host-procedure End of change
Start of changeIndicates that a call should be made to a host language procedure. After the procedure has completed, execution will continue with the next sequential instruction of the program. This clause can only be used in C, C++, ILE COBOL, and ILE RPG. The rules for the target of the call depend upon the host language. No parameters may be passed on the call nor can any results be obtained from the call.End of change
Start of changeDO EXSR subroutine End of change
Start of changeIndicates that an RPG subroutine should be called with the RPG Invoke Subroutine (EXSR) operation. After the subroutine has completed, execution will continue with the next sequential instruction of the program. This clause can only be used in ILE RPG.End of change
Start of changeDO PERFORM host-label End of change
Start of changeIndicates that a COBOL procedure should be called with the COBOL PERFORM statement. After the procedure has completed, execution will continue with the next sequential instruction of the program. This clause can only be used in ILE COBOL. The host-label can be a section-name or an unqualified paragraph-name.End of change

Notes

WHENEVER statement scope: 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.

SQL does support nested programs in COBOL, C, and RPG. However, SQL does not honor normal COBOL, C, and RPG scoping rules. That is, the last WHENEVER statement specified in the program source prior to the nested procedure is still in effect for that nested program. The label referenced in the WHENEVER statement must be duplicated within that inner program. Alternatively, the inner program could specify a new WHENEVER statement.

Start of changeFor ILE RPG, the SQL TAG statement can be used to generate the host-label branch location for the GOTO. See TAG.End of change

Examples

The following statements can be embedded in a COBOL program.
  • Example 1: Go to the label HANDLER for any statement that produces an error. 
      EXEC SQL  WHENEVER SQLERROR GOTO HANDLER  END-EXEC.
  • Example 2: Continue processing for any statement that produces a warning. 
      EXEC SQL  WHENEVER SQLWARNING CONTINUE  END-EXEC.
  • Example 3: Go to the label ENDDATA for any statement that does not return data when expected to do so. 
      EXEC SQL  WHENEVER NOT FOUND GOTO ENDDATA  END-EXEC.
  • Example 4: Call the program or procedure specified by the RPG prototype ERRORPROC for any statement that produces an error.
      EXEC SQL  WHENEVER SQLERROR DO ERRORPROC();
  • Example 5: Call the program or procedure specified by the C prototype errorproc for any statement that produces an error.
      EXEC SQL  WHENEVER SQLERROR DO errorproc();