Multiple Condition Handlers | Teradata Vantage - Multiple Condition Handlers in a Stored Procedure - Advanced SQL Engine - Teradata Database

The example in this section is based on the following stored procedure:

CREATE PROCEDURE spSample10() BEGIN   DECLARE EmpCount INTEGER;   DECLARE CONTINUE HANDLER    FOR SQLSTATE '42000'    H1:BEGIN         -- Statement_10_1    UPDATE Employee    SET Ename = 'John';         -- Suppose column Ename has been dropped.    -- Statement_10_1 returns SQLSTATE code '52003' that is    -- defined for the handler within the    -- block that activates this handler.         -- Statement_10_2    INSERT INTO Proc_Error_Table (:SQLSTATE,       CURRENT_TIMESTAMP, 'spSample10', 'Failed to Insert Row');    END H1;      DECLARE EXIT HANDLER    FOR SQLSTATE '52003' INSERT INTO Proc_Error_Table (:SQLSTATE,   CURRENT_TIMESTAMP, 'spSample10', 'Column does not exist');      DECLARE CONTINUE HANDLER    FOR SQLWARNING INSERT INTO Proc_Error_Table (:SQLSTATE,   CURRENT_TIMESTAMP, 'spSample10', 'Warning has occurred'); DECLARE CONTINUE HANDLER    FOR NOT FOUND INSERT INTO Proc_Error_Table (:SQLSTATE,  CURRENT_TIMESTAMP, 'spSample10', 'No data found'); -- Statement_10_3 UPDATE Employee  SET Salary_Amount = 10000  WHERE Employee_Number = 1001;     -- Statement_10_4 INSERT INTO EmpNames VALUES (1002, 'Thomas');   -- Suppose table EmpNames has been dropped. -- Statement_10_4 returns SQLSTATE '42000' that is -- handled. -- Statement_10_5 UPDATE Employee  SET Salary_Amount = 10000  WHERE Employee_Number = 1003;    -- Statement_10_6 SELECT COUNT(*) INTO EmpCount FROM Employee SAMPLE 5; -- Suppose table Employee has only three rows. -- Statement_10_6 returns SQLSTATE 'T7473' that is -- handled by SQLWARNING handler. -- Statement_10_7 DELETE Employee WHERE Employee_Number = 1; -- Suppose table Employee does not have a row for -- Employee_Number = 1. Statement_10_7 returns SQLSTATE -- '02000' that is handled by NOT FOUND handler. END;

Example: ANSI Session Mode for Multiple Condition Handlers

This example assumes that the following three SQL statements are invoked interactively from BTEQ in ANSI session mode:

INSERT INTO Department VALUES ('10', 'Development');      UPDATE Employee    SET Salary_Amount = 10000    WHERE Employee_Number = 1000;     CALL spSample10();

When the preceding three SQL statements are invoked in ANSI session mode, the following sequence of events occurs:

1. Statement_10_4 in the called stored procedure raises an exception with SQLSTATE code '42000' that is handled using a CONTINUE handler.
2. While performing the handler action for SQLSTATE '42000', Statement_10_1 raises an exception with SQLSTATE code '52003'.

   Because an exception raised by a handler cannot be handled outside the handler action clause, control does not pass to the handler for SQLSTATE code '52003'.

3. The procedure terminates and returns the original SQLSTATE code '42000' to the caller.
4. The following statements are not executed:

   Statement_10_2

   Statement_10_5

   Statement_10_6

   Statement_10_7

5. The following statements remain active in a transaction that is not yet committed:

   The first two interactive SQL statements

   Statement_10_3

6. End of process.