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.
