DECLARE HANDLER (SQLWARNING Type) | Teradata Vantage - DECLARE HANDLER (SQLWARNING Type) - 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™

SQLWARNING is a generic condition that represents the SQLSTATE codes for all completion conditions (other than successful completion and “no data found” conditions).

The handler associated with SQLWARNING is invoked when a completion condition is raised during statement execution, and a handler to handle the specific completion condition does not exist.

An SQLWARNING handler can be of EXIT type or CONTINUE type.

SQLWARNING Handler Actions

The flow of control for an SQLWARNING generic condition handler is similar to the flow of control for the SQLEXCEPTION handler. The difference is that an SQLWARNING handler is activated by a raised completion condition.

SQLWARNING cannot handle a “no data found” condition. An SQLWARNING handler, if declared in a stored procedure either along with a NOT FOUND handler or separately, is not activated by a “no data found” completion condition.

Example: Generic Condition Handler for DECLARE HANDLER (SQLWARNING Type)

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

CREATE PROCEDURE spSample11()
BEGIN
  DECLARE EmpCount INTEGER;
  DECLARE EXIT HANDLER
  FOR SQLWARNING
    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
  SELECT COUNT(*) INTO EmpCount FROM Employee SAMPLE 5;
    
  -- Suppose table Employee has only three rows.
  -- Statement_11_2  returns SQLSTATE 'T7473' that is
  -- handled by the SQLWARNING handler.
END;

Example: ANSI Session Mode for DECLARE HANDLER (SQLWARNING 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 a completion condition with SQLSTATE 'T7473' that is handled by the SQLWARNING handler because no specific handler exists for SQLSTATE code 'T7473'.
  2. Because the condition handler is of EXIT type, control passes to the caller after the handler action finishes.
  3. The following items remain active and uncommitted:
    • The first two interactive SQL statements
    • Statement_11_1
    • The INSERT statement inside the handler
  4. End of process.

Example: Specific and Generic Condition Handlers

The following example illustrates the behavior of an SQLWARNING 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 EmpCount INTEGER DEFAULT 0;
  -- Handler_1
 DECLARE CONTINUE HANDLER
    FOR SQLWARNING
    BEGIN
      UPDATE warning_table
        SET warning_count = warning_count + 1;
      INSERT INTO Proc_Error_Table (:SQLSTATE,
        CURRENT_TIMESTAMP, 'spSample12', 'Generic warning handler');
    END;
  
  -- Handler_2
  DECLARE EXIT HANDLER FOR SQLSTATE 'T7473'
    INSERT INTO Proc_Error_Table (:SQLSTATE,
    CURRENT_TIMESTAMP, 'spSample12', 'Requested sample is larger than table rows');
    
  -- Statement_12_1
  UPDATE Employee
    SET Salary_Amount = 10000
    WHERE Employee_Number = 1001;
    
  -- Statement_12_2
  SELECT COUNT(*) INTO EmpCount FROM Employee SAMPLE 5;
    
  -- Suppose the table Employee has only three rows.
  -- Statement_12_2 returns SQLSTATE 'T7473' that is
  -- handled by specific handler.
END;

Example: ANSI Session Mode Calling spSample12();

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 called stored procedure raises a completion condition with SQLSTATE code 'T7473' that is handled by the specific handler Handler_2.
  2. Handler_2 executes its handler action. Because Handler_2 is an EXIT handler, and the procedure has only one compound statement, the procedure terminates after the handler action completes.
  3. The following items remain active, but are not committed:
    • The first two interactive SQL statements
    • Statement_12_1
    • Action statement for Handler_2
  4. End of process.