DECLARE HANDLER (SQLEXCEPTION Type) | Teradata Vantage - DECLARE HANDLER (SQLEXCEPTION Type) - Advanced SQL Engine - Teradata Database

SQLEXCEPTION is a generic condition that represents the SQLSTATE codes for all exception conditions. The handler associated with SQLEXCEPTION is invoked when an exception condition is raised during statement execution and a handler to handle the specific exception condition does not exist.

An SQLEXCEPTION handler can be written as an EXIT handler or as a CONTINUE handler.

SQLEXCEPTION Handler Actions

The following table describes the flow of control for an SQLEXCEPTION handler when it is activated by a raised exception:

1. A statement in the stored procedure raises an exception.
2. The generic condition handler is invoked if no handler exists to handle the specific exception condition.
3. An SQLEXCEPTION handler executes its designated action.
4. The next stage in the process depends on the handler type.

   IF the handler is this type …	THEN control passes to the …
   CONTINUE	next statement in the current block.
   EXIT	end of the current block.

5. Interaction with specific handlers varies depending on the situation.
6. End of process.

Example: Generic Condition Handler

The following example illustrates the behavior of an SQLEXCEPTION handler. The example assumes the following stored procedure:

CREATE PROCEDURE spSample11()
BEGIN
  DECLARE hNumber INTEGER;
  DECLARE EXIT HANDLER
  FOR SQLEXCEPTION
    INSERT INTO Proc_Error_Table (:SQLSTATE,
      CURRENT_TIMESTAMP, ’spSample11’, ’Generic
                             handler performed’);
  -- Statement_11_1
  UPDATE Employee
    SET Salary_Amount = 10000
     WHERE Employee_Number = 1001;
        
-- Statement_11_2
  INSERT INTO EmpNames VALUES (1002, ’Thomas’);
    
  -- If the EmpNames table had been dropped,
  -- Statement_11_2 returns SQLSTATE ’42000’ that is handled.
    
  -- Statement_11_3
   UPDATE Employee
    SET Salary_Amount = 10000
     WHERE Employee_Number = 1003;
END;

Example: ANSI Session Mode for DECLARE HANDLER (SQLEXCEPTION Type)

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 spSample11();

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

1. Statement_11_2 in the called stored procedure raises an error condition with SQLSTATE ‘42000’ that is handled by the SQLEXCEPTION handler because no specific handler exists for SQLSTATE code ‘42000’.
2. Statement_11_2 is rolled back.
3. Because the condition handler is an EXIT handler, control passes to the caller after the handler action finishes.

   If the stored procedure has nested blocks, control passes to the next statement following the calling compound statement.

4. The following items remain active and uncommitted:
   • The first two interactive SQL statements
   • Statement_11_1
   • The INSERT statement inside the handler
5. End of process.

Example: Behavior of an SQLEXCEPTION and Specific Condition Handlers when Both DECLARE HANDLER Forms Are Combined

The following example illustrates the behavior of an SQLEXCEPTION handler and a specific condition handler when both DECLARE HANDLER forms are combined in a stored procedure. The example assumes the following stored procedure:

CREATE PROCEDURE spSample12()
BEGIN
  DECLARE hNumber INTEGER;
  DECLARE CONTINUE HANDLER
    FOR SQLEXCEPTION
  -- Handler_1
    BEGIN
      UPDATE exception_table
        SET exception_count = exception_count + 1;
      INSERT INTO Proc_Error_Table (:SQLSTATE,
        CURRENT_TIMESTAMP, ’spSample12’, 'Failed to insert                                      row');
    END;
  DECLARE EXIT HANDLER FOR SQLSTATE ’53000’
  -- Handler_2
    INSERT INTO Proc_Error_Table (:SQLSTATE,
     CURRENT_TIMESTAMP, ’spSample12’, ’Column does not exist’);
    
  -- Statement_12_1
  UPDATE Employee
    SET Salary_Amount = 10000
    WHERE Employee_Number = 1001;
       
  -- Statement_12_2
  INSERT INTO EmpNames VALUES (1002, ’Thomas’);
       
  -- If the EmpNames table has been dropped, Statement_12_2  
-- returns the SQLSTATE code ’42000’ that is handled
       
  -- Statement_12_3
  
  UPDATE Employee
    SET Salary_Amount = 10000
    WHERE Employee_number = 1003;
       
 -- If Salary_Amount has been dropped,  -- Statement_12_3
returns the SQLSTATE code ’53000’ that is handled
    
END;

Example: ANSI Session Mode for Specific and Generic 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 spSample12();

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

1. Statement_12_2 in the stored procedure raises an exception with SQLSTATE code ‘42000’ that is not handled because no condition handler exists.
2. Statement_12_2 is rolled back.
3. The generic SQLEXCEPTION handler, named Handler_1 by a comment, is activated.

   On successful completion of the handler action, Statement_12_3 executes because Handler_1 is a CONTINUE handler.

4. Statement_12_3 raises an exception condition with SQLSTATE code ‘53000’.
5. Control passes to Handler_2, which is explicitly defined to handle that SQLSTATE condition.
6. Handler_2 executes its handler action. Because Handler_2 is an EXIT handler, control passes to the end of the block after the handler action completes.

   The procedure terminates if it does not contain any other statements.

7. The following items remain active, but are not committed:
   • The first two interactive SQL statements
   • Statement_12_1
   • Action statement for Handler_1
   • Action statement for Handler_2
8. End of process.

Related Topics

For more information about SQLEXCEPTION Handler Actions, see Example: Behavior of an SQLEXCEPTION and Specific Condition Handlers when Both DECLARE HANDLER Forms Are Combined.