SQL Server – 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');

<strong>BEGIN TRAN</strong>
EXEC ('UPDATE #T SET COL1 = ''5'' WHERE ID = 3')
<strong>ROLLBACK TRAN</strong>

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 ('
<strong>BEGIN TRAN</strong>
UPDATE #T SET COL1 = ''5'' WHERE ID = 3
<strong>ROLLBACK TRAN</strong>
')
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:

<strong>BEGIN TRAN</strong>
EXEC ('
UPDATE #T SET COL1 = ''5'' WHERE ID = 3
<strong>ROLLBACK TRAN</strong>
')

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.