EXCEPTION HANDLERS

Translation reference to convert Teradata EXCEPTION HANDLERS clause to Snowflake Scripting

Description

Teradata's single and multiple Exception Handlers are replaced by its equivalent handlers in Snowflake Scripting.

For more information regarding Teradata EXCEPTION HANDLERS, check here.

DECLARE < handler_type > HANDLER
  FOR  < condition_value_list > < handler_action > ;

Sample Source Patterns

SQLEXCEPTION HANDLER

Teradata

Single handler

CREATE PROCEDURE handlerSample ()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        INSERT INTO Proc_Error_Table ('procSample', 'Failed SqlException');
    SELECT * FROM Proc_Error_Table;
END;

Multiple handlers

CREATE PROCEDURE handlerSample ()
BEGIN
    DECLARE ConditionByUser1 CONDITION;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        INSERT INTO Proc_Error_Table ('procSample', 'Failed SqlException');
    DECLARE EXIT HANDLER FOR ConditionByUser1
        INSERT INTO Proc_Error_Table ('procSample', 'Failed ConditionByUser1');
    SELECT * FROM Proc_Error_Table;
END;

Snowflake Scripting

Single handler

CREATE OR REPLACE PROCEDURE PUBLIC.handlerSample ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      SELECT * FROM PUBLIC.Proc_Error_Table;
   EXCEPTION
      WHEN other THEN
         INSERT INTO PUBLIC.Proc_Error_Table VALUES ('procSample', 'Failed SqlException');
   END;
$$;

Multiple handlers

CREATE OR REPLACE PROCEDURE PUBLIC.handlerSample ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      ConditionByUser1 EXCEPTION;
   BEGIN
      SELECT * FROM PUBLIC.Proc_Error_Table;
   EXCEPTION
      WHEN ConditionByUser1 THEN
         INSERT INTO PUBLIC.Proc_Error_Table VALUES ('procSample', 'Failed ConditionByUser1');
      WHEN other THEN
         INSERT INTO PUBLIC.Proc_Error_Table VALUES ('procSample', 'Failed SqlException');
   END;
$$;

User-Defined Handlers

Teradata

Query

CREATE PROCEDURE handlerSample ()
BEGIN
    DECLARE EXIT HANDLER FOR Custom1, Custom2, Custom3
      BEGIN
        SET Message1 = 'custom1 and custom2 and custom3';
      END;
    SELECT * FROM Proc_Error_Table;
END;

Snowflake Scripting

Query

CREATE OR REPLACE PROCEDURE PUBLIC.handlerSample ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      SELECT * FROM PUBLIC.Proc_Error_Table;
   EXCEPTION
      WHEN Custom1 OR Custom2 OR Custom3 THEN
         BEGIN Message1 := 'custom1 and custom2 and custom3';
      END;
   END;
$$;

Known Issues

CONTINUE Handler

A 'CONTINUE' handler in Teradata allows the execution to be resumed after executing a statement with errors. This is not supported by the exception blocks in Snowflake Scripting. Condition Handler Teradata reference documentation.

Teradata

Query

CREATE PROCEDURE handlerSample ()
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        INSERT INTO Proc_Error_Table ('spSample4', 'Failed SqlException');
    SELECT * FROM Proc_Error_Table;
END;

Snowflake Scripting

Query

CREATE OR REPLACE PROCEDURE PUBLIC.handlerSample ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      /*** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'CONTINUE MODE HANDLER' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/
      /*    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION INSERT INTO PUBLIC.Proc_Error_Table VALUES ('spSample4', 'Failed SqlException');*/
      SELECT * FROM PUBLIC.Proc_Error_Table;
   END;
$$;

Other not supported handlers

Handlers for SQLSTATE, SQLWARNING, and NOT FOUND are not supported

Teradata

Query

CREATE PROCEDURE handlerSample ()
BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '42002', SQLWARNING, NOT FOUND
        INSERT INTO Proc_Error_Table ('procSample', 'Failed SqlState or SqlWarning or Not Found');
    SELECT * FROM Proc_Error_Table;
END;

Snowflake Scripting

Query

CREATE OR REPLACE PROCEDURE PUBLIC.handlerSample ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      /*** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'SQLSTATE, SQLWARNING, NOT-FOUND TYPES HANDLER' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/
      /*    DECLARE EXIT HANDLER FOR SQLSTATE '42002', SQLWARNING, NOT FOUND INSERT INTO PUBLIC.Proc_Error_Table VALUES ('procSample', 'Failed SqlState or SqlWarning or Not Found');*/
      SELECT * FROM PUBLIC.Proc_Error_Table;
   END;
$$;

Related EWIS

1. MSCEWI1058: Functionality is not currently supported by Snowflake Scripting