Rolling Back Dynamic SQL

Do transactions apply in dynamic SQL, in SQL Server? In a word, yes. For proof, run the following chunks of code:

No Transactions

You can see the third row having a value of “5”, showing that it was updated

CREATE TABLE #T(
ID INT IDENTITY(1,1)
,COL1 VARCHAR(100)
,COL2 VARCHAR(100)
);

INSERT INTO #T (COL1,COL2) VALUES('1','2');
INSERT INTO #T (COL1,COL2) VALUES('1','2');
INSERT INTO #T (COL1,COL2) VALUES('1','2');
INSERT INTO #T (COL1,COL2) VALUES('1','2');
INSERT INTO #T (COL1,COL2) VALUES('1','2');
EXEC ('UPDATE #T SET COL1 = "5" WHERE ID = 3')
SELECT * FROM #T;
DROP TABLE #T;

Transaction Around the Dynamic SQL Call

The results show that the UPDATE statement was successfully rolled back.

CREATE TABLE #T(
ID INT IDENTITY(1,1)
,COL1 VARCHAR(100)
,COL2 VARCHAR(100)
);

INSERT INTO #T (COL1,COL2) VALUES('1','2');
INSERT INTO #T (COL1,COL2) VALUES('1','2');
INSERT INTO #T (COL1,COL2) VALUES('1','2');
INSERT INTO #T (COL1,COL2) VALUES('1','2');
INSERT INTO #T (COL1,COL2) VALUES('1','2');

BEGIN TRAN
EXEC ('UPDATE #T SET COL1 = "5" WHERE ID = 3')
ROLLBACK TRAN

SELECT * FROM #T;
DROP TABLE #T;

Transaction Within the Dynamic SQL

The results here also show that the UPDATE statement was rolled back.

CREATE TABLE #T(
ID INT IDENTITY(1,1)
,COL1 VARCHAR(100)
,COL2 VARCHAR(100)
);

INSERT INTO #T (COL1,COL2) VALUES('1','2');
INSERT INTO #T (COL1,COL2) VALUES('1','2');
INSERT INTO #T (COL1,COL2) VALUES('1','2');
INSERT INTO #T (COL1,COL2) VALUES('1','2');
INSERT INTO #T (COL1,COL2) VALUES('1','2');
EXEC ('
BEGIN TRAN
UPDATE #T SET COL1 = "5" WHERE ID = 3
ROLLBACK TRAN
')
SELECT * FROM #T;
DROP TABLE #T;

Both ways work successfully. Your placement of the BEGIN/ROLLBACK TRAN commands can vary based on where the code is that you want to possibly rollback.

One thing that does not work is this:

BEGIN TRAN
EXEC ('
UPDATE #T SET COL1 = "5" WHERE ID = 3
ROLLBACK TRAN
')

That throws the error:

Msg 266, Level 16, State 2, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.