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.
FOR this condition type … | AND this session mode … | This action is taken by the Transaction Manager … |
---|---|---|
Error | ANSI | a request-level rollback. Only the statement that raised the exception condition is rolled back. |
Failure |
|
a transaction-level rollback. All updates executed within the transaction are rolled back. |
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;