The following transaction may contain nested transactions and this is considered a complex pattern not supported in Snowflake.
Some parts in the output code are omitted for clarity reasons.
Severity
High
Description
This error is added to indicate when a transaction may contain nested transactions. In SQL Server, transactions can be nested. This means that it is possible to start a new transaction within an existing transaction. If after the first BEGIN statement, we execute another one, a new transaction will be opened and the current transaction count will be increased by one.
On the other hand this is not supported in Snowflake, what will happen is that the second BEGIN statement will be ignored and we will still have only one transaction. For more information please refer to SQL Server Transactions.
Code Example
Input Code:
IN -> SqlServer_01.sql
CREATE PROC transactionsTest
AS
BEGIN TRANSACTION
SELECT @@TRANCOUNT AS TransactionCount_AfterFirstTransaction
INSERT INTO TESTSCHEMA.TESTTABLE(ID) VALUES (1), (2)
BEGIN TRANSACTION
SELECT @@TRANCOUNT AS TransactionCount_AfterSecondTransaction
INSERT INTO TESTSCHEMA.TESTTABLE(ID) VALUES (3), (4)
COMMIT;
SELECT @@TRANCOUNT AS TransactionCount_AfterFirstCommit
COMMIT;
END;
Output Code:
OUT -> SqlServer_01.sql
CREATE OR REPLACE PROCEDURE transactionsTest ()
RETURNS ARRAY
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
ProcedureResultSet1 VARCHAR;
ProcedureResultSet2 VARCHAR;
ProcedureResultSet3 VARCHAR;
return_arr ARRAY := array_construct();
BEGIN
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0009 - THE FOLLOWING TRANSACTION MAY CONTAIN NESTED TRANSACTIONS WHICH ARE NOT SUPPORTED IN SNOWFLAKE. ***/!!!
BEGIN TRANSACTION;
ProcedureResultSet1 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet1) AS
SELECT
:TRANCOUNT AS TransactionCount_AfterFirstTransaction;
return_arr := array_append(return_arr, :ProcedureResultSet1);
INSERT INTO TESTSCHEMA.TESTTABLE (ID) VALUES (1), (2);
BEGIN TRANSACTION;
ProcedureResultSet2 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet2) AS
SELECT
:TRANCOUNT AS TransactionCount_AfterSecondTransaction;
return_arr := array_append(return_arr, :ProcedureResultSet2);
INSERT INTO TESTSCHEMA.TESTTABLE (ID) VALUES (3), (4);
COMMIT;
ProcedureResultSet3 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet3) AS
SELECT
:TRANCOUNT AS TransactionCount_AfterFirstCommit;
return_arr := array_append(return_arr, :ProcedureResultSet3);
COMMIT;
--** SSC-FDM-0020 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
RETURN return_arr;
END;
$$;
-- ** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '12' COLUMN '1' OF THE SOURCE CODE STARTING AT 'END'. EXPECTED 'BATCH' GRAMMAR. CODE '80'. **
--END
!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
;
Recommendations
In Snowflake nested transactions will not cause compilation errors, they will simply be ignored. You can access the assessment reports to check if nested transactions are present.
