Try Catch to handle exception and Commit/Rollback transaction in sql server

Introduction: In this article I am going to explain how to use try-catch block in Sql server for exception handling and rollback the transaction in case of error with suitable example in stored procedure

Implementation: Let’s create a stored procedure to delete employee details from 3 tables. We want either employee details be deleted from all the three tables or not at all i.e. delete operation should be atomic.

CREATE PROCEDURE spDeleteEmployeeDetails
    @EmpId INT   
        BEGIN TRANSACTION --Start Transaction
            DELETE FROM tbEmployeeAcademicDetails WHERE EmployeeId=@EmpId
            DELETE FROM tbEmployeeDocuments WHERE EmployeeId=@EmpId
            DELETE FROM tbEmployeeDetails WHERE EmployeeId=@EmpId
        COMMIT TRAN -- Commit transaction on success
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN --RollBack transaction in case of error
            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();

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

Explanation: As in above stored procedure, In TRY-Catch block I have wrapped the delete statements in TRANSACTION.  If all the three delete statements get succeeded, the COMMIT will be called and the transaction will get committed. But if any of these three delete queries fails, control will be transferred to CATCH block where the transaction will be rolled back i.e. it will undo the changes made to database.

Note: The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.

Error Functions used within CATCH block
  • ERROR_NUMBER() Returns the error number and its value is same as for @@ERROR function.
  • ERROR_LINE() Returns the line number of T-SQL statement that caused error.
  • ERROR_SEVERITY() Returns the severity level of the error.
  • ERROR_STATE() Returns the state number of the error.
  • ERROR_PROCEDURE() Returns the name of the stored procedure or trigger that generated the error.
  • ERROR_MESSAGE() Returns the full text of error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

The RAISEERROR statement returns error information to the calling application.
Before the release of Sql Server 2012 we use RAISEERROR to re-throw the error information to the calling application.  But with the release of SQL SERVER 2012 throwing error related data to calling application becomes easier. 
Now no need to write Error functions within Catch block. Only THROW statement is required. It is not mandatory to pass any parameter to raise an exception. To Re-THROW the original exception caught in the TRY Block, we can just specify the THROW statement without any parameters in the CATCH block. I will explain more about RAISEERROR and THROW in my next article.

Now over to you:
"A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..