SSC-EWI-TD0004

Not supported SQL Exception on continue handler.

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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]