ABORT and ROLLBACK

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

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.

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
			ROLLBACK;
			END IF;
	END;
$$;

ABORT and ROLLBACK with table references without FROM clause

Teradata

Query

IN -> Teradata_04.sql

CREATE TABLE  ReferenceTable
    (ColumnValue INTEGER);
    
REPLACE PROCEDURE procedureFromTableAbort() 
BEGIN
    ROLLBACK WHERE ReferenceTable.ColumnValue > 2;
    ABORT WHERE ReferenceTable.ColumnValue > 4;
END;

Snowflake Scripting

Abort and rollback

OUT -> Teradata_04.sql

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

CREATE OR REPLACE PROCEDURE procedureFromTableAbort ()
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
            WHERE
            ReferenceTable.ColumnValue > 2;
            IF (_ROW_COUNT > 0) THEN
            ROLLBACK;
            END IF;
            SELECT
            COUNT(*)
            INTO
            _ROW_COUNT
            FROM
            ReferenceTable
            WHERE
            ReferenceTable.ColumnValue > 4;
            IF (_ROW_COUNT > 0) THEN
            ROLLBACK;
            END IF;
    END;
$$;

Known Issues

1. Custom Error Message

Even though the ROLLBACK AND ABORT are supported, using them with a custom error message is not supported.

Teradata

Error message

IN -> Teradata_05.sql

ABORT 'Error message for abort';
ROLLBACK  'Error message for rollback';

Snowflake Scripting

Error message

OUT -> Teradata_05.sql

ABORT 'Error message for abort';
ROLLBACK  'Error message for rollback';

2. Aggregate function

The use of the aggregate function combined with ABORT/ROLLBACK is not supported

Teradata

Aggregate function

IN -> Teradata_06.sql

ROLLBACK WHERE SUM(ATable.AValue) < 2;
ABORT WHERE SUM(ATable.AValue) < 2;

Snowflake Scripting

Aggregate function

OUT -> Teradata_06.sql

ROLLBACK WHERE SUM(ATable.AValue) < 2;
ABORT WHERE SUM(ATable.AValue) < 2;

Related EWIS

No related EWIs.