T-SQL Error Handling Pattern (For Nested Transactions and Stored Procedures)

Error Handling has always been cumbersome in SQL server, with all the unstructured @@Error checks scattered all around the place, this makes the T-SQL code harder to read and many errors go unaddressed due to simple coding mistakes. SQL Server 2005 comes to the rescue with the new TRY…CATCH block, but it is important how the try catch block is used. Also, recently I have had many issues with the Nested Transactions and the Nested Stored Procedures each using its own transaction.
The issues I am referring to are the things like the TRANCOUNT check on the entry and exit of each stored procedure. For example you are in a stored procedure which is called by another outer procedure (both using BEGIN TRAN), when you call Rollback in the second procedure, SQL Server will throw the following error on the exit of the second procedure and will leave the transaction is a unconsistent state
“Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count =0. ”
Similarly, as I mentioned in a previous post a single ROLLBACK will set the @@TRANCOUNT to zero and any subsequent ROLLBACK in an outer procedure will give the following error
“The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.”
Infact, all these small things are very trivial in T-SQL and there are several ways to solve them, for example some people try not to use the transactions at database at all, they take the TRANSACTION Management completely to their data layer and manage it through MTS. (This was the case in one of the projects I worked on last year). Similarly, some people would always take care that Transaction is being used in only the outer procedure which is to be called by the data layer etc. However, this can easily break if you are not in control of all the T-SQL code you are writing,like if you have an encrypted third-party stored procedure, you wouldn’t know if it calls the ROLLBACK/COMMIT which may break your logic in the outer procedure.
There should be some standard pattern for the Error Handling and writing standard stored procedure, and especially with the availability of the new Error Handling features in SQL Server 2005. So, I came up with the following Pattern for a standard stored procedure using an independent transaction. This stored procedure will not break on the behavior of the procedure it calls and is returning the error messages to its caller in a standard way (RAISERROR)
ALTER PROCEDURE [dbo].[StandardTSQLProcedure]
AS

BEGIN TRY
DECLARE
@TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0

——————————————————-
–Your Standard T-SQL Code for the Procedure Comes Here
——————————————————-
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN(0)
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorNumber INT;
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorNumber = ERROR_NUMBER();

IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH