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

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

Snowflake Scripting

Query

CREATE OR REPLACE PROCEDURE PUBLIC.procedureBasicAbort ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
        ROLLBACK;
        ROLLBACK;
    END;
$$;

Conditional ABORT and ROLLBACK

Teradata

Query

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

Snowflake Scripting

Query

CREATE OR REPLACE PROCEDURE PUBLIC.procedureWhereAbort (ANOTHERVALUEPROC FLOAT)
RETURNS VARCHAR
LANGUAGE SQL
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

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

CREATE TABLE PUBLIC.ReferenceTable
(
ColumnValue INTEGER);

CREATE TABLE PUBLIC.ReferenceTable2
(
ColumnValue INTEGER);

CREATE OR REPLACE PROCEDURE PUBLIC.procedureFromAbort ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
        LET _ROW_COUNT FLOAT;
      
        SELECT
        COUNT(*)
        INTO _ROW_COUNT
        FROM PUBLIC.ReferenceTable, PUBLIC.ReferenceTable2
        WHERE "PUBLIC.ReferenceTable".ColumnValue = "PUBLIC.ReferenceTable2".ColumnValue;
        IF (_ROW_COUNT > 0) THEN
            ROLLBACK;
        END IF;
      
        SELECT
        COUNT(*)
        INTO _ROW_COUNT
        FROM PUBLIC.ReferenceTable, PUBLIC.ReferenceTable2
        WHERE "PUBLIC.ReferenceTable".ColumnValue = "PUBLIC.ReferenceTable2".ColumnValue;
        IF (_ROW_COUNT > 0) THEN
            ROLLBACK;
        END IF;
    END;
$$;

ABORT and ROLLBACK with table references without FROM clause

Teradata

Query

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

CREATE TABLE PUBLIC.ReferenceTable
(
ColumnValue INTEGER);

CREATE OR REPLACE PROCEDURE PUBLIC.procedureFromTableAbort ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
        LET _ROW_COUNT FLOAT;
      
        SELECT
        COUNT(*)
        INTO _ROW_COUNT
        FROM "PUBLIC.PUBLIC."ReferenceTable""
        WHERE "PUBLIC.ReferenceTable".ColumnValue > 2;
        IF (_ROW_COUNT > 0) THEN
            ROLLBACK;
        END IF;
      
        SELECT
        COUNT(*)
        INTO _ROW_COUNT
        FROM "PUBLIC.PUBLIC."ReferenceTable""
        WHERE "PUBLIC.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

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

Snowflake Scripting

Error message

ROLLBACK;
--** MSC-ERROR - MSCEWI1021 - ROLLBACK RETURN MESSAGE NOT SUPPORTED **
--'Error message for abort'

ROLLBACK;
--** MSC-ERROR - MSCEWI1021 - ROLLBACK RETURN MESSAGE NOT SUPPORTED **
--'Error message for rollback'

2. Aggregate function

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

Teradata

Aggregate function

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

Snowflake Scripting

Aggregate function

/*** MSC-WARNING - MSCEWI2024 - ABORT STATEMENT IS NOT SUPPORTED DUE TO AN AGGREGATE FUNCTION ***/
/*  ROLLBACK WHERE SUM(ATable.AValue) < 2;*/

/*** MSC-WARNING - MSCEWI2024 - ABORT STATEMENT IS NOT SUPPORTED DUE TO AN AGGREGATE FUNCTION ***/
/*  ABORT WHERE SUM(ATable.AValue) < 2;*/

Related EWIS

1. MSCEWI1021: ROLLBACK RETURN MESSAGE NOT SUPPORTED

2. MSCEWI2024: ABORT STATEMENT IS NOT SUPPORTED DUE TO AN AGGREGATE FUNCTION