Application Development Guide

Handling Conditions in SQL Procedures

Condition handlers determine the behavior of your SQL procedure when a condition occurs. You can declare one or more condition handlers in your SQL procedure for general DB2 conditions, defined conditions for specific SQLSTATE values, or specific SQLSTATE values. For more information on general conditions and on defining your own conditions, see Declaring Condition Handlers.

If a statement in your SQL procedure issues an SQLWARNING or NOT FOUND condition, and you have declared a handler for the respective condition, DB2 passes control to the corresponding handler. If you have not declared a handler for that particular condition, DB2 sets the variables SQLSTATE and SQLCODE with the corresponding values for the condition and passes control to the next statement in the procedure body.

If a statement in your SQL procedure raises an SQLEXCEPTION condition, and you declared a handler for the specific SQLSTATE or the SQLEXCEPTION condition, DB2 passes control to that handler. If DB2 successfully executes the handler, the values of SQLSTATE and SQLCODE return '00000' and 0 respectively.

If a statement in your SQL procedure raises an SQLEXCEPTION condition, and you have not declared a handler for the specific SQLSTATE or the SQLEXCEPTION condition, DB2 terminates the SQL procedure and returns to the client.

Declaring Condition Handlers

The general form of a handler declaration is:

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

When DB2 raises a condition that matches condition, DB2 passes control to the condition handler. The condition handler performs the action indicated by handler-type, and then executes SQL-procedure-statement.

handler-type

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.

UNDO

Specifies that before SQL-procedure-statement executes, DB2 rolls back any SQL operations that have occurred in the compound statement that contains the handler. After SQL-procedure-statement completes, execution continues at the end of the compound statement that contains the handler.

Note:

You can only declare UNDO handlers in ATOMIC compound statements.

condition

DB2 provides three general conditions:

NOT FOUND

Identifies any condition that results in an SQLCODE of +100 or an SQLSTATE of '02000'.

SQLEXCEPTION

Identifies any condition that results in a negative SQLCODE.

SQLWARNING

Identifies any condition that results in a warning condition (SQLWARN0 is 'W'), or that results in a positive SQL return code other than +100.

You can also use the DECLARE statement to define your own condition for a specific SQLSTATE. For more information on defining your own condition, refer to the SQL Reference.

SQL-procedure-statement

You can use a single SQL procedure statement to define the behavior of the condition handler. DB2 accepts a compound statement delimited by a BEGIN...END block as a single SQL procedure statement. If you use a compound statement to define the behavior of a condition handler, and you want the handler to retain the value of either the SQLSTATE or SQLCODE variables, you must assign the value of the variable to a local variable or parameter in the first statement of the compound block. If the first statement of a compound block does not assign the value of SQLSTATE or SQLCODE to a local variable or parameter, SQLSTATE and SQLCODE cannot retain the value that caused DB2 to invoke the condition handler.

Note:

You cannot define another condition handler within the condition handler.

The following examples demonstrate simple condition handlers:

Example: CONTINUE handler: This handler assigns the value of 1 to the local variable at_end when DB2 raises a NOT FOUND condition. DB2 then passes control to the statement following the one that raised the NOT FOUND condition.

    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE CONTINUE HANDLER FOR not_found SET at_end=1;

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

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

Example: UNDO handler: The procedure declares an UNDO condition handler for SQLSTATE 42704 without first defining a name for the SQLSTATE. The handler causes the SQL procedure to roll back the current unit of work, place the string Table does not exist into output parameter OUT_BUFFER, and exit the compound statement in which the handler is declared.

    DECLARE UNDO HANDLER FOR SQLSTATE '42704'
       BEGIN
           SET OUT_BUFFER='Table does not exist';
       END;

SIGNAL and RESIGNAL Statements

You can use the SIGNAL and RESIGNAL statements to explicitly raise a specific SQLSTATE. Use the SET MESSAGE_TEXT clause of the SIGNAL and RESIGNAL statements to define the text that DB2 displays for a custom-defined SQLSTATE.

In the following example, the SQL procedure body declares a condition handler for the custom SQLSTATE 72822. When the procedure executes the SIGNAL statement that raises SQLSTATE 72822, DB2 invokes the condition handler. The condition handler tests the value of the SQL variable var with an IF statement. If var is OK, the handler redefines the SQLSTATE value as 72623 and assigns a string literal to the text associated with SQLSTATE 72623. If var is not OK, the handler redefines the SQLSTATE value as 72319 and assigns the value of var to the text associated with that SQLSTATE.

   DECLARE EXIT CONDITION HANDLER FOR SQLSTATE '72822'
      BEGIN
          IF ( var = 'OK' )
             RESIGNAL '72623' SET MESSAGE_TEXT = 'Got SQLSTATE 72822';
          ELSE
             RESIGNAL '72319' SET MESSAGE_TEXT = var;
      END;
 
   SIGNAL SQLSTATE '72822';

For more information on the SIGNAL and RESIGNAL statements, refer to the SQL Reference.

SQLCODE and SQLSTATE Variables in SQL Procedures

To help debug your SQL procedures, you might find it useful to insert the value of the SQLCODE and SQLSTATE into a table at various points in the SQL procedure, or to return the SQLCODE and SQLSTATE values in a diagnostic string as an OUT parameter. To use the SQLCODE and SQLSTATE values, you must declare the following SQL variables in the SQL procedure body:

   DECLARE SQLCODE INTEGER DEFAULT 0;
   DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

You can also use CONTINUE condition handlers to assign the value of the SQLSTATE and SQLCODE variables to local variables in your SQL procedure body. You can then use these local variables to control your procedural logic, or pass the value back as an output parameter. In the following example, the SQL procedure returns control to the statement following each SQL statement with the SQLCODE set in a local variable called RETCODE.

   DECLARE SQLCODE INTEGER DEFAULT 0;
   DECLARE retcode INTEGER DEFAULT 0;
 
   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET retcode = SQLCODE;
   DECLARE CONTINUE HANDLER FOR SQLWARNING SET retcode = SQLCODE; 
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET retcode = SQLCODE;