Posted By

rengber on 02/23/09


Tagged

database tsql Troubleshooting ErrorHandling


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

Beppoi


TSQL Try Catch Error Handling


 / Published in: SQL
 

URL: http://www.4guysfromrolla.com/webtech/041906-1.shtml

This as it sits isn't an improvement on what happens by default. It's only useful combined with a compensating transaction or sad path in the catch block, or in allowing a loop to continue.

  1. BEGIN TRY
  2. BEGIN TRANSACTION -- Start the transaction
  3.  
  4. -- Delete the Employee's phone numbers
  5. DELETE FROM EmployeePhoneNumbers
  6. WHERE EmployeeID = @EmployeeID
  7.  
  8. -- Delete the Employee record
  9. DELETE FROM Employees
  10. WHERE EmployeeID = @EmployeeID
  11.  
  12. -- If we reach here, success!
  13. COMMIT
  14. END TRY
  15. BEGIN CATCH
  16. -- Whoops, there was an error
  17. IF @@TRANCOUNT > 0
  18. ROLLBACK
  19.  
  20. -- Raise an error with the details of the exception
  21. DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  22. SELECT @ErrMsg = ERROR_MESSAGE(),
  23. @ErrSeverity = ERROR_SEVERITY()
  24.  
  25. RAISERROR(@ErrMsg, @ErrSeverity, 1)
  26. END CATCH

Report this snippet  

Comments

RSS Icon Subscribe to comments
Posted By: pollusb on March 10, 2009

Note, this works only in SQL 2005 and better !

You need to login to post a comment.