Handling Errors in SQL Server (Try Catch & Transactions)

During development of any application, one of the most common things we need to take care of is Exception and Error handling.

TRY…CATCH blocks are the standard approach to exception handling in modern programming languages. Use and syntax are likely the same as normal programming language. Like Exception Handling in Programming Language, we can use nested Try-Catch block in SQL Server also.

Try block will catch the error and will throw it in the Catch block. Catch block then handles the scenario.

trycatch

TRANSACTIONS group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction.

Users can group two or more Transact-SQL statements into a single transaction using the following statements:

  • Begin Transaction
  • Rollback Transaction
  • Commit Transaction

If anything goes wrong with any of the grouped statements, all changes need to be aborted. The process of reversing changes is called rollback in SQL Server terminology. If everything is in order with all statements within a single transaction, all changes are recorded together in the database. In SQL Server terminology, we say that these changes are committed to the database.

Example :

CREATE PROCEDURE UpdateSales
  @SalesPersonID INT,
  @SalesAmt MONEY = 0
AS
BEGIN

  BEGIN TRY
    BEGIN TRANSACTION;
      UPDATE SalesData
      SET SalesLastYear = SalesLastYear + @SalesAmt
      WHERE SalesPersonID = @SalesPersonID;
    COMMIT TRANSACTION;
  END TRY

  BEGIN CATCH
    IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;

    -- @@TRANCOUNT returns number of active Transactions,
    -- BEGIN TRANSACTION will increment the transaction count by 1 & 
    -- COMMIT TRANSACTION will decrement the transaction count by 1
    DECLARE @ErrorNumber INT = ERROR_NUMBER();
    DECLARE @ErrorLine INT = ERROR_LINE();
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();

    PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
    PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
  END CATCH

END

How to Commit and Rollback Transaction in .NET & Sql Server

If you are sending several commands to SQL Server database, you should make it a habit to build your application to be rollback capable in order to recover all commands that were already executed. In ADO.NET, we can achieve this recovery process by using the SqlTransaction object. A SqlTransaction’s ROLLBACK method can undo any work performed since the BeginTransaction method was executed while the COMMIT method ends a transaction and makes all changes visible to other users.

Example C#:

SqlTransaction trans = null;

try
{
con.Open();
// BeginTransaction() Requires Open Connection
trans = con.BeginTransaction();

// Assign Transaction to Command
cmd.Transaction = trans;

//1st command to execute. This will be rollback if 2nd command fails
cmd.CommandText = “INSERT INTO HeaderTab (Description) VALUES (‘Inserted New Category’)”;
cmd.ExecuteNonQuery();

//2nd command to execute
cmd.CommandText = “INSERT INTO DetailedTab (Description) VALUES (‘TEST CATEGORY’)”;
cmd.ExecuteNonQuery();

trans.Commit();
}
catch
{
trans.Rollback();
Console.WriteLine(“Error while processing command. Previous execution was rollbacked”);
}
finally
{
con.Close();
}

Example SqlServer:

CREATE PROCEDURE DeleteEmployee
( @EmployeeID int )
AS
BEGIN
BEGIN TRY
 BEGIN TRANSACTION TraDel— Start the transaction

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

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

— 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
END