Using XACT_STATE

• Senthil T

  Hall of Fame

  Points: 3367

  More actions

  April 16, 2009 at 11:53 am

  #202086

  Hello,

  This is my first time trying to use XACT_STATE. I found this below example on BOL. My question, do we need this check - IF XACT_STATE() =1? If we are inside a catch block, it means something went wrong, and we would just do a rollback, right? Why are we checking for this above condition and doing a commit?

  Thanks.

  BEGIN TRY

  BEGIN TRANSACTION;

  DELETE FROM Production.Product WHERE ProductID = 980;

  COMMIT TRANSACTION;

  END TRY

  BEGIN CATCH

  IF (XACT_STATE()) = -1

  ROLLBACK TRANSACTION;

  IF (XACT_STATE()) = 1

  COMMIT TRANSACTION;

  END CATCH;

  GO

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  April 16, 2009 at 3:04 pm

  #979260

  I just want to confirm your consideration. I also don't know a reason to commit a transaction if there was an error.

  Greets

  Flo

• Senthil T

  Hall of Fame

  Points: 3367

  More actions

  April 17, 2009 at 10:35 am

  #979813

  Bump. Anyone?