Return to Snippet

Revision: 11922
at September 2, 2009 01:04 by rengber


Updated Code
BEGIN TRY
   BEGIN TRANSACTION    -- Start the transaction

   -- Delete the Employee's phone numbers
   DELETE FROM EmployeePhoneNumbers
   WHERE EmployeeID = @EmployeeID

   -- Delete the Employee record
   DELETE FROM Employees
   WHERE EmployeeID = @EmployeeID

   -- If we reach here, success!
   COMMIT
END TRY
BEGIN CATCH
  -- Whoops, there was an error
  IF @@TRANCOUNT > 0
     ROLLBACK

  -- Raise an error with the details of the exception
  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  SELECT @ErrMsg = ERROR_MESSAGE(),
         @ErrSeverity = ERROR_SEVERITY()

  RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

Revision: 11921
at February 23, 2009 21:05 by rengber


Initial Code
BEGIN
      BEGIN TRY
         DECLARE @Foo as money
         set @Foo = 1.1
         set @Foo = 'foo' 
         --RAISERROR('Foo',1,1) --Note this doesn't work.    
      END TRY
      BEGIN CATCH
           PRINT @@Error
           DECLARE @ErrMsg as NVarchar(1000)
           set @ErrMsg = ERROR_MESSAGE() 
           PRINT @ErrMsg           
      END CATCH
END

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

Initial Description
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.

Initial Title
TSQL Try Catch Error Handling

Initial Tags
database

Initial Language
SQL