SQL Server logic within EXECUTE statement ignoring transactions

I've encountered this in SQL Server 2019 (v15).

This is a simplified version of much larger stored procedure. But this shows the behavior I am puzzled by, inside the exec there is a transaction, this transaction is then committed, then error is thrown.

I would expect that this change is persisted, but it's not, looks like the whole content of EXEC statement is reverted.

Can someone explain the behavior in this scenario?

Here is the code sample to reproduce this:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table (test int);

INSERT INTO my_table VALUES (1);

BEGIN TRY
    SELECT 'start'
    EXEC(' EXEC(  ''
    BEGIN TRANSACTION; 
    SET IMPLICIT_TRANSACTIONS OFF;
    INSERT INTO my_table VALUES (2); 
    COMMIT; 
    THROW 1;
    BEGIN TRANSACTION; 
    INSERT INTO my_table VALUES (3); 
    COMMIT; 
    '' )' )
    SELECT 'after EXEC'
    THROW
END TRY
BEGIN CATCH
     EXEC ('ROLLBACK TRANSACTION;');
     SELECT 'In CATCH Block'
END CATCH

SELECT 'After END CATCH'
 
SELECT * FROM my_table;

Solution

The problem is not the transaction, it is your T-SQL, it contains a syntax error. WE can see this if we expose the error within the CATCH with something like PRINT ERROR_MESSAGE(), which shows us the error inside the TRY is:

Incorrect syntax near ';'.

A syntax error means that the entire batch is not run, and if the batch doesn't run, there is no transaction as it also never ran.

The syntax error is your THROW 1;; you can't THROW an arbitrary error number. The syntax for THROW is either THROW; or THROW <Error Number>, <Error Message>, <Error State>;; you must pass all the parameters, or none. If you are using no parameters, this is known as a "rethrow" and can only appear inside a CATCH.

For your SQL to work as you want, you need to correct the syntax, and pass 3 parameters to THROW. For example:

DROP TABLE IF EXISTS my_table;
GO
CREATE TABLE my_table (test int);
GO
INSERT INTO my_table
VALUES (1);
GO
BEGIN TRY
    SELECT 'start';
    EXEC (' EXEC(  ''
    BEGIN TRANSACTION; 
    SET IMPLICIT_TRANSACTIONS OFF;
    INSERT into my_table values (2); 
    COMMIT; 
    THROW 78912, N''''A non-descript error'''',16;
    BEGIN TRANSACTION; 
    INSERT into my_table values (3); 
    COMMIT; '' )');
    SELECT 'after EXEC' AS [throw];
END TRY
BEGIN CATCH
    EXEC ('ROLLBACK TRANSACTION;');
    SELECT 'In CATCH Block';
END CATCH;
SELECT 'After END CATCH';
GO
SELECT *
FROM my_table;
GO

DROP TABLE dbo.my_table

This returns the results you expected:

test
1
2

It also produces the follow error:

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

This is expected, as the THROW is after the COMMIT and before the next BEGIN TRANSACTION, so there was no transaction to rollback. Though, moving the THROW to after the BEGIN TRANSACTION will result in a different error.

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

When dealing with tranasctions in deffered batches, like this, you need to handle the transactions in the same scope. Jumping out to the outer scope, and then creating a new inner scope to rollback a tranasction from a completely different scope won't work.