DECLARE CONTINUE HANDLER

Translation reference to convert Teradata DECLARE CONTINUE handler to Snowflake Scripting

Description

Handle completion conditions and exception conditions not severe enough to affect the flow of control.

For more information regarding the Teradata DECLARE CONTINUE handler, check here.

DECLARE CONTINUE HANDLER FOR
  { 
    { sqlstate_state_spec | condition_name } [,...] |

    { SQLEXCEPTION | SQLWARNING | NOT FOUND } [,...]

  } handler_action_statement ;

Sample Source Patterns

DECLARE CONTINUE HANDLER

Teradata

Query

IN -> Teradata_01.sql

REPLACE PROCEDURE PURGING_ADD_TABLE
( 
 IN inDatabaseName     	VARCHAR(30), 
 IN inTableName    		VARCHAR(30)
)
BEGIN
 DECLARE vCHAR_SQLSTATE CHAR(5);
 DECLARE vSUCCESS       CHAR(5);

  DECLARE CONTINUE HANDLER FOR SQLSTATE 'T5628'
  BEGIN
     SET vCHAR_SQLSTATE = SQLCODE;
     SET vSUCCESS    = SQLCODE;
  END;

  SELECT 1;
 
END;

Snowflake Scripting

Query

OUT -> Teradata_01.sql

CREATE OR REPLACE PROCEDURE PURGING_ADD_TABLE
(INDATABASENAME STRING, INTABLENAME STRING
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
 BEGIN
  LET vCHAR_SQLSTATE CHAR(5);
  LET vSUCCESS       CHAR(5);
  BEGIN
   SELECT
    1;
  EXCEPTION
   WHEN statement_error THEN
    LET errcode := :sqlcode
    LET sqlerrmsg := :sqlerrm
    IF (errcode = '904'
    AND contains(sqlerrmsg, 'invalid value')) THEN
     BEGIN
      vCHAR_SQLSTATE := SQLCODE;
      vSUCCESS := SQLCODE;
     END;
    ELSE
     RAISE
    END IF
  END
 END;
$$;

Known Issues

DECLARE CONTINUE HANDLER FOR SQLSTATE

The support of declaring continue handlers for some SQLSTATE values is not currently supported by Snowflake Scripting.

Teradata

Query

IN -> Teradata_02.sql

CREATE PROCEDURE declareConditionExample2 ( )
BEGIN
   ...
   DECLARE CONTINUE HANDLER FOR SQLSTATE 'UNSUPPORTED'
     BEGIN
       SET vCHAR_SQLSTATE = SQLCODE;
       SET vSUCCESS    = SQLCODE;
    END;
    ...
END;

Snowflake Scripting

Query

OUT -> Teradata_02.sql

CREATE OR REPLACE PROCEDURE declareConditionExample2 ( )
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
   ...
   !!!RESOLVE EWI!!! /*** SSC-EWI-TD0004 - NOT SUPPORTED SQL EXCEPTION ON CONTINUE HANDLER ***/!!!
   DECLARE CONTINUE HANDLER FOR SQLSTATE 'UNSUPPORTED'  
   BEGIN
      vCHAR_SQLSTATE := SQLCODE;
      vSUCCESS := SQLCODE;
   END;
   ...
$$;

Related EWIS

1. SSC-EWI-TD0004: Not supported SQL Exception on continue handler.