Statement-Specific Condition Handling | Teradata Vantage - Statement-Specific Condition Handling - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
vqj1592443206677.ditamap
dita:ditavalPath
vqj1592443206677.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantage™

This section describes the behavior of various SQL control statements when they raise conditions in a stored procedure.

Cursor Handling for Exceptions in FOR Loops

The respective handling of open cursors for Failure and Error conditions are described in the following table:

IF this exception occurs while a FOR cursor loop is executing … THEN all open cursors are …
FAILURE closed as part of a transaction rollback.
ERROR not closed.

The handler action specified by the condition handler is executed only after all the open cursors have been closed.

Example: WHILE Loop Exceptions

The following example assumes the following stored procedure:

CREATE PROCEDURE spSample8()
BEGIN
  DECLARE hNumber INTEGER;
  DECLARE CONTINUE HANDLER
  FOR SQLSTATE '42000'
    INSERT INTO Proc_Error_Table
     (:SQLSTATE, CURRENT_TIMESTAMP, 'spSample8',
      'Failed to Insert Row');
  
  SET hNumber = 1;
  
  -- Statement_8_1
  UPDATE Employee SET Salary_Amount = 10000
  WHERE Employee_Number = 1001;
  
  WHILE hNumber < 10
  DO
  -- Statement_8_2
  INSERT INTO EmpNames VALUES (1002, 'Thomas');
  SET hNumber = hNumber + 1;
  END WHILE;
  -- If the EmpNames table had been dropped,
  -- Statement_8_2 returns an SQLSTATE code of    
     -- '42000' that is handled.
    
  -- Statement_8_3
  UPDATE Employee
    SET Salary_Amount = 10000
    WHERE Employee_Number = 1003;
END;

Example: ANSI Session Mode for Statement-Specific Condition Handling

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

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

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

  1. Statement_8_2 within the called stored procedure raises an error condition with SQLSTATE ‘42000’ that is handled. The statement is rolled back.
  2. The condition handler is activated.
  3. Because the handler type is CONTINUE, execution resumes from the SET statement within the WHILE loop after the handler action completes, and the WHILE loop is not exited because of the exception.
  4. During each iteration Statement_8_2 raises an exception which is handled.

    Statement_8_3 executes on termination of the WHILE loop.

  5. The following items are not committed:
    • The first two interactive SQL statements
    • Statement_8_1
    • Action statement for the condition handler
    • Statement_8_3
  6. End of process.
When stored procedure spSample8 is created in a session in Teradata session mode, the process described above applies with one difference: because every request is an implicit transaction in Teradata session mode, the following statements are committed:
  • The first two interactive SQL statements
  • Statement_8_1
  • Action statement for the condition handler
  • Statement_8_3

Example: Exceptions Raised By an IF Statement

The following example assumes the following stored procedure:

CREATE PROCEDURE spSample9()
BEGIN
  DECLARE hNumber, NumberAffected INTEGER;
  DECLARE CONTINUE HANDLER
   FOR SQLSTATE '22012'
   INSERT INTO Proc_Error_Table
    (:SQLSTATE, CURRENT_TIMESTAMP, 'spSample9',
     'Failed Data Handling');
    
  SET hNumber = 100;
    
    -- Statement_9_1
    UPDATE Employee SET Salary_Amount = 10000
      WHERE Employee_Number BETWEEN 1001 AND 1010;
    
  SET NumberAffected = ACTIVITY_COUNT;
    
  IF hNumber/NumberAffected < 10 THEN
    
  -- If the UPDATE in Statement_9_1 results in 0 rows
  -- being affected, the IF condition raises an
  -- exception with SQLSTATE '22012' that is
  -- handled.
    
  -- Statement_9_2
    INSERT INTO data_table (NumberAffected, 'DATE');
  
    SET hNumber = hNumber + 1;
   
  END IF;
    
  -- Statement_9_3
    UPDATE Employee
      SET Salary_Amount = 10000
      WHERE Employee_Number = 1003;
END;

The preceding 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 spSample9();

Consider the following sequence of events with respect to the preceding stored procedure:

  1. The IF statement in the called stored procedure raises a divide-by-zero error condition with SQLSTATE '22012' that is handled.
  2. Because the handler type is CONTINUE, execution resumes from Statement_9_3 after the handler action completes.
  3. Statement_9_2 and the SET statement are inside the IF statement that raised the error condition, so they are not executed.
  4. The updates made by the following items remain intact in a transaction that is uncommitted:
    • Statement_9_1
    • Statement_9_3
  5. End of process.

Example: Exception Raised by a Condition in WHILE Loop

The following example illustrates the behavior of a WHILE statement when a condition in the loop raises an exception. This behavior also applies to IF and FOR statements. The example assumes the following stored procedure:

CREATE PROCEDURE spSample8()
BEGIN
   DECLARE hNumber INTEGER;
   DECLARE CONTINUE HANDLER
   FOR SQLSTATE '22012'
    INSERT INTO Proc_Error_Table 
      (:SQLSTATE,  CURRENT_TIMESTAMP, 'spSample8',
    'Failed in WHILE condition');
  
   SET hNumber = 1;
   SET hNo = 0;
     
   -- Statement_8_1
   UPDATE Employee SET Salary_Amount = 10000
     WHERE Employee_Number = 1001;
    
   WHILE ((hNumber/hNo) < 10)
   DO
   -- Statement_8_2
   INSERT INTO EmpNames VALUES (1002, 'Thomas');
   SET hNumber = hNumber + 1;
  END WHILE;
      
   -- The condition in WHILE statement raises
   -- an exception and returns SQLSTATE code
   -- of 22012 that is handled.
      
   -- Statement_8_3
   UPDATE Employee
     SET Salary_Amount = 10000
     WHERE Employee_Number = 1003;
END;

Example: ANSI Session Mode

The preceding 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 spSample8();

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

  1. The condition in the WHILE statement within the called stored procedure raises an exception.
  2. The condition handler is activated.

    Since the condition handler is of CONTINUE type, control passes to the next statement after the WHILE loop (statement_8_3), and execution of the stored procedure continues from statement_8_3.

  3. Statement_8_2 and the SET statement in the WHILE loop are not executed.
  4. On completion of the stored procedure execution, the following statements are not committed:
    • The first two interactive SQL statements
    • Statement_8_1
    • Action statement for the condition handler
    • Statement_8_3
  5. When stored procedure spSample8 is created in a session in Teradata session mode, the process described above applies with one difference: because every request is an implicit transaction in Teradata session mode, the following statements are committed:
    • The first two interactive SQL statements
    • Statement_8_1
    • Action statement for the condition handler
    • Statement_8_3
  6. End of process.