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

In previous articles i posted Example to explain Transaction in Sql server using Asp.net and 20 main differences between Stored procedures and Functions in Sql Server and Example to Declare and use Table Variables in Sql and Difference between Temporary Table and Table Variable in Sql Server  and Use of Cursor in Sql with simple example

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   

AS

BEGIN

    BEGIN TRY

        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

    END TRY

    BEGIN CATCH

        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);

    END CATCH

END

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."

• Tweet
• Share
• Share
• Share

Related Post