Rollback Transaction On Error In SQL Server Stored Procedure

In my earlier article, we have seen how to capture and return the error messages from stored procedures. Now we will see how to implement transaction along with exception handling. In any transaction scenario, we have to rollback transaction on error in SQL execution. Using TRY-CATCH we can capture the exception occurred in a statement in the stored procedure and in the CATCH block we can rollback the transaction. Below is an example. In this example, In the CATCH block after capturing the error details, we can place the ROLLBACK TRANSACTION.

Example

Use WideWorldImporters
GO

/** Create Stored procedure **/
CREATE PROCEDURE mtb_SampleExceptionHndling
	@Error NVARCHAR(MAX) OUTPUT
AS
BEGIN

	BEGIN TRANSACTION;

	BEGIN TRY

		DELETE FROM Sales.Customers WHERE CustomerID = 1

	END TRY
	BEGIN CATCH

		SET @Error = 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + '; ' + Char(10) +
		'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)) + '; ' + Char(10) +
		'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10)) + '; ' + Char(10) +
		'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10)) + '; ' + Char(10) +
		'Error Message: ' + ERROR_MESSAGE()

		IF @@TRANCOUNT > 0  
			ROLLBACK TRANSACTION;
	END CATCH

	IF @@TRANCOUNT > 0  
		COMMIT TRANSACTION;

END
GO

/** Execute Stored Procedure **/
DECLARE @ErrorMsg NVARCHAR(MAX)
EXEC mtb_SampleExceptionHndling @Error = @ErrorMsg output
Select @ErrorMsg
GO

/** Result **/
-----------

(0 rows affected)

-----------
Error Number: 547; 
Error Severity: 16; 
Error State: 0; 
Error Line: 10; 
Error Message: The DELETE statement conflicted with the REFERENCE constraint 
"FK_Sales_CustomerTransactions_CustomerID_Sales_Customers". 
The conflict occurred in database "WideWorld

(1 row affected)

Rollback Transaction On Error In SQL Server Stored Procedure

Reference

  • About TRY-CATCH, and implementing transaction in stored procedure at Microsoft Docs.


1 thought on “Rollback Transaction On Error In SQL Server Stored Procedure”

  1. If @@TRANCOUNT= 0, won’t this leave an open transaction hanging about?

    Can’t you just always rollback when an exception is thrown? How much overhead is there in rolling back a transaction that changed nothing?

    Shouldn’t the commit go at the end of the try block? How much overhead is there in committing a transaction that changed nothing?

    Reply

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.