WHENEVER

WHENEVER is a directive used in an application program or a procedure to specify an action to be taken depending on the outcome of subsequent SQL statements.

Scope

Application Programs and Procedures Only

SQL Syntax

  WHENEVER {SQLERROR 
            SQLWARNING 
            NOT FOUND  } {STOP 
                          CONTINUE 
                          GOTO [:]Label 
                          GO TO [:]Label}

Parameters

SQLERROR

refers to a test for the condition SQLCODE < 0.

SQLWARNING

refers to a test for the condition SQLWARN0 = 'W'.

NOT FOUND

refers to a test for the condition SQLCODE = 100.

STOP

causes a ROLLBACK WORK statement and terminates the application program or procedure, whenever an SQL statement produces the specified condition.

CONTINUE

means no special action is taken automatically when a SQL statement produces the specified condition. Sequential execution will continue.

GOTO [:]Label

specifies a label to jump to whenever the condition is found to be true after executing a SQL statement. In an application, the label must conform to the SQL syntax rules for a basic name or any other legitimate label in the host language as well as the requirements of the host language.

In a procedure, the label is an integer or a name which conforms to the SQL syntax rules for a basic name. You can optionally include a colon (:) before the label to conform to FIPS 127.1 flagger standards.

Description

In an application, SQLCODE and SQLWARN0 are fields in the SQLCA or built-in variables. They are structures ALLBASE/SQL uses to return status information about SQL statements. In a procedure, ::sqlcode and ::sqlwarn0 are built-in variables. If the WHENEVER statement is not specified for a condition, the default action is CONTINUE.
A WHENEVER directive affects all SQL statements that come after it in the source program listing or procedure, up to the next WHENEVER directive for the same condition.
You can write code of your own to check the SQLCA for error or warning conditions, whether or not you use the WHENEVER directive.
This directive cannot be used interactively or with dynamic parameters.

Authorization

You do not need authorization to use the WHENEVER directive.

Example

Execution of the program terminates if the CONNECT TO statement cannot be executed successfully.

   INCLUDE SQLCA
    .
    .
    . 
  
   WHENEVER SQLERROR STOP
 
   CONNECT TO '.../sampledb/PartsDBE'
   .
   .
   .

If a row does not qualify, control is passed to the statement labeled 9000.

   INCLUDE SQLCA
   .
   .
   .
   WHENEVER NOT FOUND GO TO 9000
 
   SELECT OrderDate
     FROM PurchDB.Orders
    WHERE OrderNumber = :OrdNum