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
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
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": "06/18/2024" }}'
EXECUTE AS CALLER
AS
$$
DECLARE
vCHAR_SQLSTATE CHAR(5);
vSUCCESS CHAR(5);
BEGIN
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
CREATE PROCEDURE declareConditionExample2 ( )
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE 'UNSUPPORTED'
BEGIN
SET vCHAR_SQLSTATE = SQLCODE;
SET vSUCCESS = SQLCODE;
END;
END;
Snowflake Scripting
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
$$
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;
END;
$$;
Related EWIS
SSC-EWI-TD0004: Not supported SQL Exception on continue handler.
Last updated