Errorhandling and Transactions in T-SQL

• Rofty

  SSCrazy

  Points: 2585

  More actions

  September 28, 2009 at 5:21 am

  #210200

  Hey all. I have the following scenario. An Interal Account Transfer stored procedure will recieve four parameters. FromAccountKey, ToAccountKey, Date and Amount.

  What happens is a record is created for both accounts ... one record subtracts the amount from the FromAccount, the other adds the amount to the ToAccount.

  How will you catch an error that might occur on any one of the two insert statements and ensure that both records are rolled-back. What puzzle me is the part where you check for errors.

  Example:

  --BEGIN TRANS

  EXEC dbo.uspInsertAccountTransaction @AccountFrom, -@Amount, @DateKey

  EXEC dbo.uspInsertAccountTransaction @AccountTo, @Amount, @DateKey

  --IF @@Error <> 0 THEN

  --ROLLBACK TRANS

  --ELSE

  --COMMIT TRANS

  --END IF

  [font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
  😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
  -----------------------------------------------------------------------------------------------------------------------[/font]

• Mark Cowne

  One Orange Chip

  Points: 26954

  More actions

  September 28, 2009 at 6:50 am

  #1058977

  Use TRY and CATCH blocks

  BEGIN TRY

  BEGIN TRANSACTION

  EXEC dbo.uspInsertAccountTransaction @AccountFrom, -@Amount, @DateKey

  EXEC dbo.uspInsertAccountTransaction @AccountTo, @Amount, @DateKey

  COMMIT

  END TRY

  BEGIN CATCH

  IF @@TRANCOUNT>0 ROLLBACK

  SELECT ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE(),ERROR_SEVERITY(),ERROR_STATE()

  END CATCH

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Grant Fritchey

  SSC Guru

  Points: 398909

  More actions

  September 28, 2009 at 6:51 am

  #1058978

  Use the TRY/CATCH construct. It provides the most power for what you need. There's an introductory article here[/url].

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning