Advanced Search

TSQL Try Catch Error Handling

/ Published in: SQL
Save to your folder(s)
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.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.