SQL/MX 3.1 Programming Manual for C and COBOL (H06.23+, J06.12+)

Exception Handling and Error Conditions
HP NonStop SQL/MX Release 3.1 Programming Manual for C and COBOL—663854-001
13-14
Precedence of Multiple WHENEVER Declarations
Precedence of Multiple WHENEVER Declarations
When more than one WHENEVER declaration applies to an SQL statement, NonStop
SQL/MX processes the conditions in order of precedence:
1. NOT FOUND
2. SQLERROR
3. SQL_WARNING
For example, an SQL error and an SQL warning can occur for the same statement, but
the error condition has a higher precedence and is processed first.
Determining the Scope of a WHENEVER Declaration
The order in which WHENEVER declarations appear in the listing determines their
scope:

A WHENEVER declaration remains in effect until another WHENEVER declaration
for the same condition appears. If you want to execute a different routine when an
error occurs, specify a new WHENEVER declaration with a different CALL routine.
For example, in a C program, to insert a new row when a row is not found, specify
a new WHENEVER declaration:
EXEC SQL WHENEVER NOT FOUND CALL insert_row;
The new WHENEVER declaration remains in effect until it is disabled or changed.

If a WHENEVER declaration is coded in a function, the declaration remains in
effect outside of the function even if the scope of the function is no longer valid.
Therefore, if you do not want the declaration to remain in effect, disable it at the
end of the function described next in Enabling and Disabling the WHENEVER
Declaration.

A WHENEVER declaration does not affect SQL statements if they appear in the
program before WHENEVER.

If you are debugging a program and you use a WHENEVER declaration to call an
error handling procedure, you might need to save the SQLSTATE value in a local
variable within the error handling procedure. Each subsequent SQL statement
resets SQLSTATE, and you might lose a value you need for debugging.
Enabling and Disabling the WHENEVER Declaration
If you use WHENEVER SQLERROR GOTO some_label; to avoid infinite loops, enable
the WHENEVER declaration at the beginning of the program and disable WHENEVER
for the part of the program that handles the condition named in the declaration.