WHENEVER | Teradata Vantage - WHENEVER - Advanced SQL Engine - Teradata Database

Purpose

Specifies the action to be taken when an exception condition occurs.

Invocation

Executable.

Embedded SQL only.

Syntax

WHENEVER condition action

condition

A status keyword that indicates the type of condition for which the indicated action is to be undertaken.

The valid condition keywords and their definitions are listed in the following tables.

Following each keyword is the definition of the values for the SQLCODE and SQLSTATE variables when the condition occurs.

When SQLERROR is a condition in which an SQL error occurs:

• SQLCODE has the following value: <0
• When NOT FOUND is a condition in which no data is found:
  • SQLCODE has the following value: +100
  • SQLSTATE has the following value: 02xxx

When SQLWARNING is a condition in which an SQL warning occurs (SQLWARNING is a non-ANSI Teradata extension):

• SQLCODE has the following value: a positive number other than +100
• SQLSTATE is not defined.

action

The action to be executed when condition occurs.

The valid actions are:

• CONTINUE
• GO TO :host_label
• GOTO :host_label
• PERFORM code
• CALL function_call

where:

• :host_label specifies a valid target of a client language GO TO statement. Use of a preceding colon is strongly recommended.
• code specifies the name of a section or paragraph in the application to be executed when the exception condition occurs. The PERFORM action is valid only for COBOL.
• function_call specifies the function to be called when the exception condition occurs.

ANSI Compliance

WHENEVER is ANSI/ISO SQL:2011-compliant with extensions.

Authorization

None.

Rules

• If the precompiler SQLFLAGGER option is set to ENTRY, WHENEVER SQLWARNING causes a precompiler warning.
• The rules for the object of a GO TO are language-dependent.
• The initial implied exception declaration is always CONTINUE.
• An exception declaration applies to a particular SQL statement only if that statement follows the exception declaration in the text of the program and there are no intervening exception declarations for the same exception condition.

  IF an exception condition applies and the action is …	THEN the application program continues execution at the …
  CONTINUE	next sequential instruction. The exception condition is ignored.
  GOTO	specified target location. host_label must be such that a client language GO TO statement specifying that target is valid at every SQL statement to which the exception declaration applies.
  CALL	next sequential instruction only after the specified subprogram has been executed (called) and control has been returned to the calling program. A corresponding client statement (CALL function call for COBOL and PL/I or function call for C) must be valid at every SQL statement to which the exception declaration applies.
  PERFORM	next sequential instruction only after the specified COBOL paragraphs or sections have been executed. A corresponding COBOL statement (PERFORM code) must be valid at every SQL statement to which the exception declaration applies.

• The following SQLCODE definitions apply:

  IF SQLCODE has this value following the execution of an SQL statement …	THEN the following exception condition applies …
  any negative number	SQLERROR
  a positive number other than +100	SQLWARNING
  +100	NOT FOUND

Related Topics