How to Handle Exception and Error in SQL Server 2000 and SQL Server 2005 (Transactions – Rollback Transaction, Commit Transaction)

SQL Server 2000 doesn’t have a standard and structured way to handle exception in SQL Query. However, we can user the standard error variable @@ERROR to check whether any error has occurred in order to take the necessary action.

Example for SQL Server 2000 to handle exception:-

BEGIN TRANSACTION
DECLARE @Err AS INT
-- Put your statements here.
SET @Err = @@ERROR
IF @Err <> 0
  BEGIN
    ROLLBACK TRANSACTION
    SELECT 'The transaction is rollbacked due to exception or error.' AS RESULT
  END
ELSE
  BEGIN
  COMMIT TRANSACTION
  SELECT 'The Transaction is committed.' AS RESULT
  END

However, SQL Server 2005 has come with a structured way to handle the exception (Try-Catch blocks). This structure is very similar to those used in C#, VB.Net and Java.

Example for SQL Server 2005 to handle exception :-

BEGIN TRY
  BEGIN TRANSACTION
  -- Put your statements here.
  COMMIT TRANSACTION
  SELECT 'The Transaction is committed.' AS RESULT
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION
  SELECT 'The transaction is rollbacked due to exception or error.' AS RESULT
END CATCH