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
REPLACE PROCEDURE procedureBasicAbort()
BEGIN
ABORT;
ROLLBACK;
END;
Snowflake Scripting
CREATE OR REPLACE PROCEDURE PUBLIC.procedureBasicAbort ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
ROLLBACK;
ROLLBACK;
END;
$$;
Conditional ABORT and ROLLBACK
Teradata
REPLACE PROCEDURE procedureWhereAbort(AnotherValueProc INTEGER)
BEGIN
ABORT WHERE AValueProc > 2;
ROLLBACK WHERE (AnotherValueProc > 2);
END;
Snowflake Scripting
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
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
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
CREATE TABLE ReferenceTable
(ColumnValue INTEGER);
REPLACE PROCEDURE procedureFromTableAbort()
BEGIN
ROLLBACK WHERE ReferenceTable.ColumnValue > 2;
ABORT WHERE ReferenceTable.ColumnValue > 4;
END;
Snowflake Scripting
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
ABORT 'Error message for abort';
ROLLBACK 'Error message for rollback';
Snowflake Scripting
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
ROLLBACK WHERE SUM(ATable.AValue) < 2;
ABORT WHERE SUM(ATable.AValue) < 2;
Snowflake Scripting
/*** 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
MSCEWI1021: ROLLBACK RETURN MESSAGE NOT SUPPORTED
MSCEWI2024: ABORT STATEMENT IS NOT SUPPORTED DUE TO AN AGGREGATE FUNCTION
Last updated
Was this helpful?