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.