Skip to content
benjf.com
benjf.com

Real content from a human brain

  • Home
  • About
  • ✞ Faith
  • Opinion
    • Politics
    • Health
    • Movies
    • Music
    • ☺ For Fun
    • Random Awesomeness
  • Technology
    • Desktop
    • Android
    • ✉ Email
    • Privacy
    • Programming
      • HTML
      • CSS
      • Javascript
        • jQuery
      • PHP
      • SQL
        • SQL Server
      • Powershell
      • MS Access
      • WordPress
  • Productivity
benjf.com

Real content from a human brain

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

Post navigation

Previous post
Next post

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • Love Never Fails
  • FIXED! Left Audio Only on OBS with Behringer U-Phoria UMC204HD
  • Gnome Alt+Tab Window Switching; How to ungroup the windows
  • Pay or else
  • 1Password App Integration With Browser Extension on Kubuntu (or Debian Linux)

Recent Comments

  1. LOL on Major Federal Budget Cuts – do the math
  2. Writer #1 on Online Privacy In The United States
  3. More About Sortable Dates; Plus AutoHotKey Scripts! – benjf.com on Date/Time Formatting Can Unite The World
  4. AutoHotKey Tips – benjf.com on Date/Time Formatting Can Unite The World
  5. Kevin on Moving FDLAUNCHERLOG in SQL Server

Archives

  • May 2025
  • April 2025
  • March 2025
  • February 2025
  • January 2025
  • December 2024
  • November 2024
  • October 2024
  • September 2024
  • August 2024
  • July 2024
  • June 2024
  • April 2024
  • March 2024
  • November 2023
  • October 2023
  • September 2023
  • July 2023
  • June 2023
  • May 2023
  • October 2022
  • September 2022
  • August 2022
  • May 2022
  • December 2021
  • November 2021
  • September 2021
  • July 2021
  • June 2021
  • April 2021
  • March 2021
  • February 2021
  • January 2021
  • December 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • February 2020
  • January 2020
  • December 2019
  • November 2019
  • October 2019
  • July 2019
  • May 2019
  • April 2019
  • January 2019
  • December 2018
  • November 2018
  • May 2018
  • April 2018
  • February 2018
  • December 2017
  • September 2017
  • June 2017
  • May 2017
  • February 2017
  • January 2017
  • December 2016
  • August 2016
  • July 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016
  • December 2015
  • September 2015
  • August 2015
  • July 2015
  • June 2015
  • October 2013
  • September 2013
  • August 2013
  • June 2013
  • May 2013
  • April 2013
  • March 2013
  • February 2013
  • January 2013
  • December 2012
  • October 2012
  • September 2012
  • July 2012
  • April 2012
  • March 2012
  • February 2012
  • January 2012
  • December 2011
  • November 2011
  • October 2011
  • September 2011
  • August 2011
  • July 2011
  • June 2011
  • May 2011
  • April 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • September 2010
  • August 2010
  • July 2010
  • June 2010
  • May 2010
  • April 2010
  • March 2010
  • February 2010
  • January 2010
  • December 2009
  • November 2009
  • October 2009

Categories

  • Ai
  • Android
  • C#
  • CSS
  • Desktop
  • EMail
  • Faith
  • For Fun
  • Health
  • HTML
  • Javascript
  • jQuery
  • Just Info
  • Linux
  • Movies
  • MS Access
  • Music
  • Opinion
  • PHP
  • Politics
  • Powershell
  • Privacy
  • Productivity
  • Programming
  • Random Awesomeness
  • Self Hosting
  • SQL
  • SQL Server
  • Technology
  • Uncategorized
  • WordPress
©2025 benjf.com | WordPress Theme by SuperbThemes