SQL Server – Rolling Back Dynamic SQL Writer #1, 2013-06-18 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. Programming SQL dynamic sqlrollbacksql servertransactions