The Scope of WHENEVER

WHENEVER is a preprocessor directive, rather than an executable statement. The preprocessor, not the database server, handles the interpretation of the WHENEVER statement. When the preprocessor encounters a WHENEVER statement in the source file, it inserts appropriate code into the preprocessed code after each SQL statement, based on the exception and the action that WHENEVER specifies. The scope of the WHENEVER statement begins where the statement appears in the source module and remains in effect until the preprocessor encounters one or the other of the following things while sequentially processing the source module:
  • The next WHENEVER statement with the same condition (SQLERROR, SQLWARNING, or NOT FOUND) in the same source module
  • The end of the source module

The following example program has three WHENEVER statements, two of which are WHENEVER SQLERROR statements. Line 4 uses STOP with SQLERROR to override the default CONTINUE action for errors.

Line 8 specifies the CONTINUE keyword to return the handling of errors to the default behavior. For all SQL statements between lines 4 and 8, the preprocessor inserts code that checks for errors and halts program execution if an error occurs. Therefore, any errors that the INSERT statement on line 6 generates cause the program to stop.

After line 8, the preprocessor does not insert code to check for errors after SQL statements. Therefore, any errors that the INSERT statement (line 10), the SELECT statement (line 11), and DISCONNECT statement (line 12) generate are ignored. The SELECT statement, however, does not stop program execution if it does not locate any rows; the WHENEVER statement on line 7 tells the program to continue if such an exception occurs: 
1   main()
2   {
3   EXEC SQL connect to 'test';
4   EXEC SQL WHENEVER SQLERROR STOP;
5   printf("\n\nGoing to try first insert\n\n");
6   EXEC SQL insert into test_color values ('green');
7   EXEC SQL WHENEVER NOT FOUND CONTINUE;
8   EXEC SQL WHENEVER SQLERROR CONTINUE;
9   printf("\n\nGoing to try second insert\n\n");
10   EXEC SQL insert into test_color values ('blue');
11   EXEC SQL select paint_type from paint where color='red';
12   EXEC SQL disconnect all;
13   printf("\n\nProgram over\n\n");
14   }