Exception Condition Transaction Semantics | Teradata Vantage - Exception Condition Transaction Semantics - Advanced SQL Engine - Teradata Database

CONTINUE and EXIT exception conditions are governed by the same set of rules. Stored procedure behavior is consistent with the transaction semantics of ANSI and Teradata session modes in general.

The following table describes the transaction semantics for error and failure conditions, respectively.

Example: Using a CONTINUE Handler Associated with An Outer Compound Statement

This example illustrates how a CONTINUE handler associated with an outer compound statement handles an exception condition raised in an inner compound statement.

CREATE PROCEDURE spSample1(IN pName CHARACTER(30), IN pAmt INTEGER)
BEGIN
   DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
     INSERT INTO Proc_Error_Tbl VALUES (:SQLSTATE,
      CURRENT_TIMESTAMP, 'spSample1', 'Duplicate Row Error');
     ...
      L1: BEGIN
     DECLARE counter INTEGER DEFAULT 5;
     DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'
       L2: BEGIN
         INSERT INTO Proc_Error_Tbl VALUES (:SQLSTATE,
                     CURRENT_TIMESTAMP, 'spSample1',
                                  'Table does not exist');
       ...
       END L2;
   WHILE (counter < 1022012) 
   DO
     INSERT INTO tab1 VALUES (pName, pAmt) ;
      -- Duplicate row error
           SET counter = counter + 1;
   END WHILE;
   ...
   END L1;
   ...
END;

Assume that table tab1 is created as follows:

CREATE SET TABLE tab1(c1 CHARACTER(30), c2 INTEGER);

Now execute the stored procedure spSample1:

CALL spSample1('Richard', 100);

The INSERT within the WHILE statement in L1 raises a duplicate row exception. Since there is no handler within the compound statement L1 to handle this exception, it is propagated to the outer compound statement, which has no label.

The CONTINUE handler declared for SQLSTATE '23505' is invoked. This handler handles the exception condition and the stored procedure execution continues from the statement following the INSERT that raised the condition.

Example: Exceptions Raised in An Inner Compound Statement

This example illustrates how an exception raised in an inner compound statement remains unhandled in the absence of a suitable handler in the entire stored procedure.

CREATE PROCEDURE spSample1(IN pName CHARACTER(30), IN pAmt INTEGER)
L1: BEGIN
...
L2: BEGIN
  DECLARE vName CHARACTER(30);
  INSERT INTO tab1 VALUES (pName, pAmt);
  -- The table does not exist
  -- exception is raised, and not handled
  SET vName = pName;
END L2;
INSERT ...
END L1;

Assume that table tab1 is dropped:

DROP TABLE tab1;

Now execute the stored procedure spSample1.

CALL spSample1('Richard', 10000);

During stored procedure execution, the first INSERT statement raises an exception with SQLSTATE '42000' but there is no handler defined to handle this exception in the compound statement labeled L2.

No handler is defined for the raised exception even in the outer compound statement labeled L1, so the stored procedure terminates with the error code corresponding to SQLSTATE '42000'.

Example: Reusing the SQLSTATE Code

The following example shows the valid reuse of the same SQLSTATE code in nested compound statements.

Assume that the table tDummy is dropped before executing the stored procedure. The same kind of exception condition occurs in the compound statements labeled L1 and L2 and the condition is handled on both occasions. The stored procedure is created with two compilation warnings.

CREATE PROCEDURE spSample (OUT po1 VARCHAR(50),
                           OUT po2 VARCHAR(50))
BEGIN
     DECLARE i INTEGER DEFAULT 0;
     L1: BEGIN
         DECLARE var1 VARCHAR(25) DEFAULT 'ABCD';
         DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'
             SET po1 = "Table does not exist in L1';
         INSERT INTO tDummy (10, var1);
      -- Table does not exist.
         L2: BEGIN
             DECLARE var1 VARCHAR(25) DEFAULT 'XYZ';
             DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'
                 SET po2 = "Table does not exist in L2';
             INSERT INTO tDummy (i, var1);
      -- Table does not exist.
         END L2;
     END L1;
END;