SSC-EWI-TD0004

Not supported SQL Exception on continue handler.

Severity

Low

Description

In Snowflake procedures there is no equivalent transformation for Teradata Continue Handler. For some supported Exception codes, Snowconvert does some treatment to emulate this behavior. This EWI is added to Continue Handler statements having an exception code that is not supported.

Example Code

Teradata:

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 'UNSUPPORTED'
  BEGIN
     SET vCHAR_SQLSTATE = SQLCODE;
     SET vSUCCESS    = SQLCODE;
  END;

  SELECT 1;
 
END;

Snowflake Scripting:

OUT -> Teradata_01.sql

CREATE OR REPLACE PROCEDURE PURGING_ADD_TABLE
(INDATABASENAME VARCHAR(30), INTABLENAME VARCHAR(30)
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/04/2024" }}'
EXECUTE AS CALLER
AS
$$
 DECLARE
  vCHAR_SQLSTATE CHAR(5);
  vSUCCESS       CHAR(5);
 BEGIN
   
   
  !!!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;
  SELECT
   1;
 END;
$$;

Recommendations

• Check the possible statements that can throw the exception code and encapsulate them in a similar code block as seen in Continue Handler Translation Reference.

• If you need more support, you can email us at [email protected]