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

Advertisements

7 thoughts on “T-SQL Error Handling Pattern (For Nested Transactions and Stored Procedures)

  1. I came across this blog entry. I wanted to try it out, but am wondering about the middle section, the “Your Standard T-SQL Code for the Procedure Comes Here” section.

    Here is an example. Right now I have procA and procB. Both can be called independently. Both have a BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN in each. Both Procs have statements that are immediately followed by an @@error check that triggers a rollback.

    But inside procA’s transaction block, there is an EXEC call to procB:

    some random insert
    if @@error 0 GOTO on_error

    DECLARE @rc int
    EXEC @rc = up_procB
    IF @rc 1 GOTO on_error

    some random insert
    if @@error 0 GOTO on_error

    COMMIT TRAN

    on_error:
    ROLLBACK TRAN

    Are you saying that in your example, where I have my “Standard T-SQL Code for the Procedure” that is surrounded by your sample code, I should just have the statements WITHOUT the @@error checks? How would procA know about the rollback issued by procB so I can trigger its “error” routine as well?

  2. Tim, Aleem’s solution is the same as the “single-level model” solution in the link you provide. The important distinction to make is that the article in your link has a critical error in the”single-level model” in that it does not set @LocalTran properly. Aleem’s solution is error free.

  3. Restrict your flirting to your crush so they don’t get confused. Starting in level 51 you will face chocolate squares. Should you get the top ranking on Google, Yahoo and MSN for your keywords, nobody can knock you off unless they do a better optimization job than you do.

  4. Pingback: Transactions best practices [ANSWERED] - Tech ABC to XYZ

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s