Search This Blog

Showing posts with label ROLLBACK. Show all posts
Showing posts with label ROLLBACK. Show all posts

Friday, November 19, 2010

Try Catch block in a Transaction

We can use TRY catch blocks from SQL Server 2005 onwards and can rollback a transaction if there were any errors. Please see the example below


BEGIN TRY
 SET XACT_ABORT ON -- Will rollback on any errors
 BEGIN TRANSACTION    -- Start the transaction
   DELETE FROM Mytable
WHERE id = 'something'
   -- 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