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 .

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 .

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.

PreviousMSCCP0008 NextMSCCP0010

Last updated 1 year ago

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;

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; $$;