SQL Programming Manual for TAL

Error and Status Processing
HP NonStop SQL Programming Manual for TAL—527887-001
6-7
Using the WHENEVER Directive
Using the CALL Format
If you use the CALL format of WHENEVER to call an error handling procedure, follow
these guidelines:
•
Specify the WHENEVER directive globally and precede the directive with a forward
declaration of the error handling procedure(s).
•
Declare the error handling procedure or subprocedure without parameters, and do
not allow it to return a value.
•
Ensure that the error handling procedure or subprocedure you call is accessible
from each SQL statement affected by the WHENEVER directive.
Using the GOTO Format
If you use the GOTO (or GO TO) format, specify the WHENEVER directive at the
beginning of the procedure containing the GOTO and disable it at the end of the
procedure. For example:
PROC error^proc;
BEGIN
EXEC SQL WHENEVER SQLERROR GOTO :error^handler;
RETURN;
...
error^handler:
... ! error handling routine
EXEC SQL WHENEVER SQLERROR; ! Disable WHENEVER
END; ! End of error^proc
Using an Aggregate Function
All aggregate functions except COUNT return a null value when operating on an empty
set. If a host variable receives the null value as the result of an aggregate function, you
must specify an indicator variable and test the result of the indicator variable.
Otherwise, NonStop SQL returns a “no indicator variable provided” condition instead of
a “no rows found” condition. A WHENEVER NOT FOUND directive does not detect this
condition.
Example of Using WHENEVER Directives
This example inserts two column values into the PARTS table and checks for errors
using WHENEVER directives. The WHENEVER SQLWARNING and WHENEVER
SQLERROR directives refer to parts of the program that handle the respective
condition.