Nested transactions Part 2 – Error Handling

By Ellis MillerBest Practice, Tutorial

In the previous article I talked about using savepoints for nested transactions. In this article will talk about how to handle rollbacks, and what to do when the Stored Procedures you use have their own transaction handling.

There was a quote that I read once, I can’t remember where I got it from, but it went something like this…

‘The best way to introduce a bug is to change something, anything.’

Just make sure that some sort of impact assessment and test plan are done if there is any change to an existing Stored Procedure.

Adding Transactions to Stored Procedures – the Microsoft Way

This is what Microsoft suggests for dealing with transactions inside a Stored Procedure.

CREATE PROCEDURE dbo.spAddParameter (@name NVARCHAR(50), @value NVARCHAR(50))
AS
BEGIN
	DECLARE @count BIT = @@TRANCOUNT
	IF @count > 0
		SAVE TRANSACTION AddParameter
	ELSE 
		BEGIN TRANSACTION

	BEGIN TRY
		INSERT INTO dbo.Parameters(Name, Value) VALUES (@name, @value)

		IF @count = 0 -- else leave commit to parent transaction
			COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		PRINT 'Error in spAddParameter: ' + ERROR_MESSAGE()
		IF @count = 0
			ROLLBACK TRANSACTION
		ELSE
			-- Check if the transaction is still valid
			IF XACT_STATE() <> -1
				ROLLBACK TRANSACTION AddParameter
		-- Raise the error to the caller
		DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
		DECLARE @ErrorSeverity INT = ERROR_SEVERITY()
		DECLARE @ErrorState INT = ERROR_STATE()

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

This script checks to see if there is a current transaction, and if so, it uses a save point, else it uses a transaction. Same with the rollback.

This is great in theory, but l’ve never seen it implemented and not sure that I ever will.

However the major aspect is this script, for me, is its use of RAISERROR. When an error is raised, you can at least be sure that you will know about it.

Should a Stored procedure always throw an error?

One of things I struggle with when debugging systems, is hidden errors. Something doesn’t work because an error is handled (well squashed) and never reported. In my opinion all underlying Stored Procedures should throw any errors they produce, and it should be up to the logic part of the app to evaluate the errors as being important or not.

That’s a general statement, you know your app better than I do.

So what if an underlying Stored Procedure hides errors?

If the underlying Stored Procedure has a transaction, then you would certainly know it if a rollback occurred, as the below error will be thrown when the execution returns. So simply wrapping the stored procedure in a try catch will handle it.

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

However, if the Stored Procedure doesn’t have a rollback, and just hides the error, then you will need to decide if you should alter the proc to add a RAISERROR.

Consuming Stored Procedures without savepoints

In this is example we are using stored procedures with and without error handling in an all or nothing transaction.

The below code produces a truncation error because the field length is only 10, but the proc accepts up to 50.

Both spAddParameter1 and spAddParameter2 throw errors so they can be used in exactly the same way. The biggest difference is that with spAddParameter2, I protect against a second rollback in my calling code with the use of IF @@TRANCOUNT > 0.

CREATE TABLE dbo.Parameters (Name NVARCHAR(10) NOT NULL, Value NVARCHAR(50) NOT NULL)
GO

CREATE PROCEDURE dbo.spAddParameter1(@name NVARCHAR(50), @value NVARCHAR(50))
AS
BEGIN
	-- this proc has no error handling
	INSERT INTO dbo.Parameters(Name, Value) VALUES (@name, @value)
END
GO

CREATE PROCEDURE dbo.spAddParameter2(@name NVARCHAR(50), @value NVARCHAR(50))
AS
BEGIN
	BEGIN TRANSACTION
	BEGIN TRY
		INSERT INTO dbo.Parameters(Name, Value) VALUES (@name, @value)
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		PRINT 'Error in spAddParameter2: ' + ERROR_MESSAGE()
		ROLLBACK TRANSACTION
		-- Raise the error to the caller
		DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
		DECLARE @ErrorSeverity INT = ERROR_SEVERITY()
		DECLARE @ErrorState INT = ERROR_STATE()
		RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
	END CATCH
END
GO

-- Add some data
PRINT 'Running handling errors in a transaction'
BEGIN TRANSACTION ProblemTransaction
BEGIN TRY
	EXECUTE dbo.spAddParameter2 @name = 'paramName1', @value = 'Value 1'
	EXECUTE dbo.spAddParameter2 @name = 'paramName2', @value = 'Value 2'
	EXECUTE dbo.spAddParameter2 @name = 'parameterName3', @value = 'Value 3' -- Produces a truncation error

	COMMIT TRANSACTION ProblemTransaction
END TRY
BEGIN CATCH
	PRINT 'Top Level Error ' + CAST(ERROR_NUMBER() AS NVARCHAR(10)) + ': ' +  ERROR_MESSAGE()
	IF @@TRANCOUNT > 0 -- this is the simple fix
		ROLLBACK TRANSACTION
END CATCH
SELECT * FROM dbo.Parameters

-- clean up
DROP TABLE IF EXISTS dbo.Parameters;
DROP PROCEDURE IF EXISTS dbo.spAddParameter1;
DROP PROCEDURE IF EXISTS dbo.spAddParameter2;

PRINT 'Trans Count = ' + CAST(@@TRANCOUNT AS NVARCHAR(5))

The above example only uses spAddParameter2, but play around with it, try commenting out the RAISERROR, or use spAddParameter1. Either way everything is handled in an all or nothing transaction.

Using savepoints

In this example I am using savepoints to do individual rollbacks so the transaction is not all or nothing. The error is the same as above, but in this script I am using a loop to call the Stored Procedure.

CREATE TABLE dbo.Parameters (Name NVARCHAR(10) NOT NULL, Value NVARCHAR(50) NOT NULL)
GO

CREATE PROCEDURE dbo.spAddParameter1(@name NVARCHAR(50), @value NVARCHAR(50))
AS
BEGIN
	-- this proc has no error handling
	INSERT INTO dbo.Parameters(Name, Value) VALUES (@name, @value)
END
GO

PRINT 'Running Transaction with Save Points'
-- create a table for the data so we can iterate through it
DECLARE @Parameters TABLE (ID INT IDENTITY NOT NULL, Name NVARCHAR(50), Value NVARCHAR(50))
INSERT INTO	@Parameters	(Name, Value)
VALUES ('paramName1', 'value 1'), ('paramName2', 'value 2'), ('parameterName3', 'value 3')

DECLARE @ID INT = 0, @Name NVARCHAR(50), @Value NVARCHAR(50)
BEGIN TRANSACTION
BEGIN TRY
	-- for each row in @Parameters, insert the values
	WHILE EXISTS(SELECT ID FROM @Parameters WHERE ID >@ID)
	BEGIN
		SELECT TOP(1) @ID = ID, @Name = Name, @Value = Value FROM @parameters WHERE ID > @ID ORDER BY ID
		SAVE TRANSACTION ParameterInsert
		BEGIN TRY
			EXECUTE dbo.spAddParameter1 @name = @Name, @value = @Value 
		END TRY
		BEGIN CATCH
			PRINT 'Error: ' + CAST(ERROR_NUMBER() AS NVARCHAR(10)) + ': ' +  ERROR_MESSAGE()
			ROLLBACK TRANSACTION ParameterInsert
		END CATCH
	END

	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	PRINT 'Top Level Error ' + CAST(ERROR_NUMBER() AS NVARCHAR(10)) + ': ' +  ERROR_MESSAGE()
	IF @@TRANCOUNT > 0 -- this is the simple fix
		ROLLBACK TRANSACTION
END CATCH


-- Output the results
SELECT * FROM dbo.Parameters

-- clean up
DROP TABLE IF EXISTS dbo.Parameters;
DROP PROCEDURE IF EXISTS dbo.spAddParameter1;

-- Check the transaction count
PRINT 'Trans Count = ' + CAST(@@TRANCOUNT AS NVARCHAR(5))

As you might expect, the results are the first two rows, and no Top Level Error.

Main Points

  • Know how the Stored Procedures you are consuming are dealing with error handling.
  • If it is up to you, make your Stored Procedures throw errors.
  • IF @@TRANCOUNT > 0 is your friend.
  • Use Save Points if you need to roll back only a portion of your execution batch.

Leave a Reply

Your email address will not be published. Required fields are marked *