Handlers in an SQL procedure

If an error occurs when an SQL procedure executes, the procedure ends unless you include statements to tell the procedure to perform some other action. These statements are called handlers.

Handlers are similar to WHENEVER statements in external SQL application programs. Handlers tell the SQL procedure what to do when an error or warning occurs, or when no more rows are returned from a query. In addition, you can declare handlers for specific SQLSTATEs. You can refer to an SQLSTATE by its number in a handler, or you can declare a name for the SQLSTATE and then use that name in the handler.

The general form of a handler declaration is:

DECLARE handler-type HANDLER FOR condition SQL-procedure-statement;

In general, the way that a handler works is that when an error occurs that matches condition, the SQL-procedure-statement executes. When the SQL-procedure-statement completes, Db2 performs the action that is indicated by handler-type.

Types of handlers

The handler type determines what happens after the completion of the SQL-procedure-statement. You can declare the handler type to be either CONTINUE or EXIT:

CONTINUE: Specifies that after SQL-procedure-statement completes, execution continues with the statement after the statement that caused the error.
EXIT: Specifies that after SQL-procedure-statement completes, execution continues at the end of the compound statement that contains the handler.

Example: CONTINUE handler: This handler sets flag at_end when no more rows satisfy a query. The handler then causes execution to continue after the statement that returned no rows.

DECLARE CONTINUE HANDLER FOR NOT FOUND SET at_end=1;

Example: EXIT handler: This handler places the string 'Table does not exist' into output parameter OUT_BUFFER when condition NO_TABLE occurs. NO_TABLE is previously declared as SQLSTATE 42704 (name is an undefined name). The handler then causes the SQL procedure to exit the compound statement in which the handler is declared.

DECLARE NO_TABLE CONDITION FOR '42704';
⋮
DECLARE EXIT HANDLER FOR NO_TABLE
 SET OUT_BUFFER='Table does not exist';