You are here

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.
Categories: 
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
13 + 1 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.

Theme by Danetsoft and Danang Probo Sayekti inspired by Maksimer