Advanced Search

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