You are here

Error message

  • Deprecated function: Array and string offset access syntax with curly braces is deprecated in include_once() (line 20 of /home/benjfc5/public_html/main/includes/file.phar.inc).
  • Deprecated function: Unparenthesized `a ? b : c ? d : e` is deprecated. Use either `(a ? b : c) ? d : e` or `a ? b : (c ? d : e)` in include_once() (line 1387 of /home/benjfc5/public_html/main/includes/bootstrap.inc).
  • Deprecated function: implode(): Passing glue string after array is deprecated. Swap the parameters in drupal_get_feeds() (line 394 of /home/benjfc5/public_html/main/includes/common.inc).

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.
3 + 0 =
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