SSC-EWI-TS0009

The following transaction may contain nested transactions and this is considered a complex pattern not supported in Snowflake.

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. **
--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.

• If you need more support, you can email us at [e-mail]

Related EWI

1. SSC-FDM-0020: Multiple result sets are returned in temporary tables

2. SSC-EWI-0001: Unrecognized token on the line of the source code.

3. SSC-EWI-0040: Statement Not Supported.