Search This Blog

Showing posts with label Try Catch in Sql Server. Show all posts
Showing posts with label Try Catch in Sql Server. 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