Condition Handler Rules | Teradata Vantage - Condition Handler Rules - Advanced SQL Engine - Teradata Database

• You can declare condition handlers for exception conditions and completion conditions other than successful completion. No condition handler can be defined for successful completion (SQLSTATE =  ‘00000’).
• You can declare condition handlers only within a compound statement.

  No handlers can be declared in stored procedures that do not contain a compound statement.

• You cannot repeat the same SQLSTATE code in a DECLARE HANDLER statement.
• You cannot declare the same SQLSTATE code for multiple condition handlers in the same compound statement.

  However, the same SQLSTATE code can be reused for condition handlers in other nested or non-nested compound statements within a stored procedure.

• You can specify SQLEXCEPTION, SQLWARNING, NOT FOUND, or any combination of these generic conditions in a handler declaration.
• You can declare each generic condition handler at most once in a compound statement.

  The same generic condition can be reused in other compound statements within a stored procedure.

• You cannot declare a specific SQLSTATE value and one or more generic conditions within the same DECLARE HANDLER statement.
• When you specify multiple statements for handler action, all the statements must be contained within a BEGIN … END compound statement.

  You can submit nested compound statements for handler action.

• The scope of a condition handler is the compound statement in which it is declared, including all nested compound statements.

Example: Using a Condition Name and Its Associated SQLSTATE Value in a Handler

The following example illustrates the usage of a condition name and its associated SQLSTATE value in a handler. The condition declaration defines condition name divide_by_zero and associates it with SQLSTATE '22012'. The EXIT handler is defined to handle divide_by_zero. During stored procedure execution, the divide-by-zero exception with SQLSTATE '22012' is raised and is handled by the EXIT handler. After successful completion of the EXIT handler statements, control exits the compound statement cs1, and the stored procedure completes successfully.

CREATE PROCEDURE condsp1 (INOUT IOParam2 INTEGER,
                          OUT OParam3 INTEGER)
cs1: BEGIN
   DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
   DECLARE EXIT HANDLER
      FOR divide_by_zero, SQLSTATE '42000'
         SET OParam3 = 0;
   SET IOParam2 = 0;
   SET OParam3 = 20/IOParam2;    /* raises exception 22012 */
END cs1;

Example: Associating a Handler Action with Multiple Condition Names

The following example illustrates the association of the same handler action with multiple condition names. A CONTINUE handler is defined for condition names divide_by_zero and table_does_not_exist. During stored procedure execution, the CONTINUE handler can handle both exceptions ERRAMPEZERODIV (SQLCODE 2802 and SQLSTATE '22012') and ERRTEQTVNOEXIST (SQLCODE 3807 and SQLSTATE '42000').

CREATE PROCEDURE condsp2 (INOUT IOParam2 INTEGER,
                          OUT OParam3 CHAR(30))
Cs1: BEGIN
   DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
   DECLARE table_does_not_exist CONDITION FOR SQLSTATE '42000';
   DECLARE CONTINUE HANDLER
      FOR divide_by_zero, table_does_not_exist
         SET OParam3 = 0;
   SET IOParam2=0;
   SET OParam3 = 20/IOParam2;    /* raises exception 22012 */
   INSERT notab VALUES (IOParam2 + 20); /* raises exception 42000 */
END Cs1;
BTEQ> DROP TABLE notab;
BTEQ> CALL condsp2(IOParam2, OParam3);

Example: Using Handlers for Generic Conditions and Explicitly Declared Conditions

The following example illustrates using different handlers to handle generic conditions and explicitly declared conditions. The second declared handler handles divide-by-zero exceptions. The first handler declared for SQLEXCEPTION handles all other exception conditions.

CREATE PROCEDURE condsp3 (OUT OParam3 INTEGER)
cs1: BEGIN
   DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
   DECLARE EXIT HANDLER
      FOR SQLEXCEPTION
         SET OParam3 = 0;
   DECLARE EXIT HANDLER
      FOR divide_by_zero
         SET OParam3 = 1;
   ...
END cs1;

Example: Results from Using the Same Handler for Both Declared and Generic Conditions

You cannot use the same handler to handle both declared conditions and generic conditions. The handler in this example is defined for both SQLEXCEPTION and condition name divide_by_zero. During stored procedure compilation, error SPL1082 will be reported, and the stored procedure will not be created.

CREATE PROCEDURE condsp4 (OUT OParam3 INTEGER)
cs1: BEGIN
   DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
   DECLARE EXIT HANDLER
      FOR SQLEXCEPTION, divide_by_zero
         SET OParam3 = 0;
   ...
END cs1;

Example: Defining the Handle Condition and the SQLSTATE

You cannot declare a handler for a condition name and the SQLSTATE value associated with the condition name in the same handler. In this example, the handler is defined to handle condition name divide_by_zero and the SQLSTATE value '22012' associated with divide_by_zero. During stored procedure compilation, error SPL1054 will be reported, and the stored procedure will not be created.

CREATE PROCEDURE condsp5 (OUT OParam3 INTEGER)
cs1: BEGIN
   DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
   DECLARE EXIT HANDLER
      FOR divide_by_zero, SQLSTATE '22012'
         SET OParam3 = 0;
   ...
END cs1;

Example: Results When Declaring Two Handlers for the Same Condition Name

You cannot declare multiple handlers for the same condition name within the same compound statement. In this example, two handlers are defined to handle the same condition name, divide_by_zero, in the compound statement cs1. During stored procedure compilation, error SPL1052 will be reported, and the stored procedure will not be created.

CREATE PROCEDURE condsp6 (OUT OParam3 INTEGER)
cs1: BEGIN
   DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
   DECLARE EXIT HANDLER
     FOR divide_by_zero
         SET OParam3 = 0;
   DECLARE EXIT HANDLER
      FOR divide_by_zero
         SET OParam3 = 1;
   ...
END cs1;

Example: Results When Declaring Handlers for a Condition Name and SQLSTATE Value

You cannot declare a handler for a condition name and another handler for the SQLSTATE value associated with the condition name within the same compound statement. In this example, the first handler is defined to handle the condition name divide_by_zero. The second handler is defined for SQLSTATE '22012' which is associated with divide_by_zero. Both handlers are defined within the compound statement cs1. Therefore, during stored procedure compilation, error SPL1054 will be reported, and the stored procedure will not be created.

CREATE PROCEDURE condsp7 (OUT OParam3 INTEGER)
cs1: BEGIN
   DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
   DECLARE EXIT HANDLER
      FOR divide_by_zero
         SET OParam3 = 0;
   DECLARE EXIT HANDLER
      FOR SQLSTATE '22012'
         SET OParam3 = 1;
   ...
END cs1;

Related Topics

For more information about:

• Specifying a condition name and a generic condition in the same handler declaration, see Example: Using Handlers for Generic Conditions and Explicitly Declared Conditions and Example: Results from Using the Same Handler for Both Declared and Generic Conditions.
• Specifying a condition name and the SQLSTATE value associated with the condition name in the same handler declaration, see Example: Defining the Handle Condition and the SQLSTATE.
• Declaring multiple handler declarations, see Example: Results When Declaring Two Handlers for the Same Condition Name.
• Declaring a handler for a condition name, see Example: Results When Declaring Handlers for a Condition Name and SQLSTATE Value.