ABORT and ROLLBACK

Translation reference to convert Teradata ABORT and ROLLBACK statements to Snowflake Scripting

Description

Teradata's ABORT and ROLLBACK statements are replaced by a ROLLBACK statement in Snowflake Scripting.

For more information on Teradata ABORT and for ROLLBACK.

ABORT [abort_message] [FROM option] [WHERE abort_condition];

ROLLBACK [WORK] [abort_message] [FROM clause] [WHERE clause];

Sample Source Patterns

Basic ABORT and ROLLBACK

Teradata

Query

IN -> Teradata_01.sql

REPLACE PROCEDURE procedureBasicAbort() 
BEGIN
    ABORT;	
    ROLLBACK;
END;

Snowflake Scripting

Query

OUT -> Teradata_01.sql

CREATE OR REPLACE PROCEDURE procedureBasicAbort ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        ROLLBACK;
        ROLLBACK;
    END;
$$;

Conditional ABORT and ROLLBACK

Teradata

Query

IN -> Teradata_02.sql

REPLACE PROCEDURE procedureWhereAbort(AnotherValueProc INTEGER) 
BEGIN
    ABORT WHERE AValueProc > 2;
	
    ROLLBACK WHERE (AnotherValueProc > 2);
END;

Snowflake Scripting

Query

OUT -> Teradata_02.sql

CREATE OR REPLACE PROCEDURE procedureWhereAbort (ANOTHERVALUEPROC INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/23/2024" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        IF (AValueProc > 2) THEN
            ROLLBACK;
        END IF;
        IF (AnotherValueProc > 2) THEN
            ROLLBACK;
        END IF;
    END;
$$;

ABORT and ROLLBACK with table references and FROM clause

Teradata

Query

IN -> Teradata_03.sql

CREATE TABLE  ReferenceTable
    (ColumnValue INTEGER);
  
CREATE TABLE  ReferenceTable2
    (ColumnValue INTEGER);

REPLACE PROCEDURE procedureFromAbort() 
BEGIN
    ROLLBACK FROM ReferenceTable, ReferenceTable2
	WHERE ReferenceTable.ColumnValue = ReferenceTable2.ColumnValue;
    ABORT FROM ReferenceTable, ReferenceTable2
        WHERE ReferenceTable.ColumnValue = ReferenceTable2.ColumnValue;
END;

Snowflake Scripting

Query

OUT -> Teradata_03.sql

CREATE OR REPLACE TABLE ReferenceTable
(
	ColumnValue INTEGER)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

CREATE TABLE ReferenceTable2
(
	ColumnValue INTEGER)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

CREATE OR REPLACE PROCEDURE procedureFromAbort ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
	BEGIN
		LET _ROW_COUNT FLOAT;
		SELECT
			COUNT(*)
		INTO
			_ROW_COUNT
			FROM
			ReferenceTable,
			ReferenceTable2
				WHERE
			ReferenceTable.ColumnValue = ReferenceTable2.ColumnValue;
			IF (_ROW_COUNT > 0) THEN
			ROLLBACK;
			END IF;
			SELECT
			COUNT(*)
			INTO
			_ROW_COUNT
			FROM
			ReferenceTable,
			ReferenceTable2
			        WHERE
			ReferenceTable.ColumnValue = ReferenceTable2.ColumnValue;
			IF (_ROW_COUNT > 0) THEN