MSCCP0009

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

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Medium

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:

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:

CREATE OR REPLACE PROCEDURE transactionsTest ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      --** MSC-ERROR - MSCCP0009 - THIS TRANSACTION MAY CONTAIN NESTED TRANSACTIONS AND THIS IS CONSIDERED A COMPLEX PATTERN NOT SUPPORTED IN SNOWFLAKE. **
      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;
$$;

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 [email protected]