@@TRANCOUNT = 2?

• ScottPletcher

  SSC Guru

  Points: 101207

  More actions

  January 26, 2021 at 3:17 pm

  #3837645

  @@TRANCOUNT itself is fine, I think.  Does UPDATE increment and decrement @@TRANCOUNT as it processes?!

  CREATE TABLE #foo (i INT NOT NULL);
  INSERT INTO #foo(i) VALUES (0);
  SET NOCOUNT ON;
  
  SELECT 'A', @@TRANCOUNT
  
  BEGIN TRANSACTION;                    -- @@TRANCOUNT = 1
      SELECT 'B', @@TRANCOUNT
    BEGIN TRANSACTION;                  -- @@TRANCOUNT = 2
      SELECT 'C', @@TRANCOUNT
      UPDATE #foo SET i = @@TRANCOUNT;  -- @@TRANCOUNT = 3
      SELECT 'D', @@TRANCOUNT
    COMMIT TRANSACTION;
      SELECT 'E', @@TRANCOUNT
  COMMIT TRANSACTION;
      SELECT 'G', @@TRANCOUNT
  SELECT i FROM #foo;                   -- Returns 3
  
  BEGIN TRANSACTION;                    -- @@TRANCOUNT = 1
      SELECT 'I', @@TRANCOUNT
    UPDATE #foo SET i = @@TRANCOUNT;    -- @@TRANCOUNT = 2
  COMMIT TRANSACTION;
      SELECT 'J', @@TRANCOUNT
  SELECT i FROM #foo;                   -- Returns 2
  
      SELECT 'K', @@TRANCOUNT
  UPDATE #foo SET i = @@TRANCOUNT;      -- @@TRANCOUNT should be 1
  SELECT i FROM #foo;                   -- Returns 2?
  
  DROP TABLE #foo;
  
  SET NOCOUNT OFF;
  
  

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• Goldner

  Grasshopper

  Points: 11

  More actions

  February 1, 2021 at 9:14 am

  #3839556

  Thanks for the info. I am looking for the same info you shared. Keep suggesting such informative post.

   

   

   

   

  myaarpmedicare.com

  • This reply was modified 4 years, 9 months ago by Goldner.
  • This reply was modified 4 years, 9 months ago by Goldner.