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)

Reference

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