TSQL try catch transaction error handling, transaction count mismatch

I have a sproc that I am calling from C# with a transaction by doing:

using (var dbContext = PowerToolsDatabase.GetDataContext())
{
    dbContext.Connection.Open();
    using (dbContext.Transaction = dbContext.Connection.BeginTransaction(System.Data.IsolationLevel.Serializable))
    { 
        foreach (var element in package.AddOrUpdateElements)
        {
            dbContext.usp_Element_Commit( /* args */);
        }

        dbContext.Transaction.Commit();
    }
}

And in that sproc there is a try catch, and a RAISERROR inside the try part that is executed under certain circumstances

BEGIN TRY
    BEGIN TRANSACTION   
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    -- Perform Name Uniqueness check (for new)
    IF EXISTS ( SELECT PK.identifier --... )
    BEGIN

        RAISERROR(60000, 16, 1, 'dbo.usp_Element_Commit', 'Supplied Element Name (for new Element) already exists')

        RETURN

    END

    -- Do stuff         

    COMMIT TRANSACTION
END TRY
BEGIN CATCH

    IF XACT_STATE() <> 0
    BEGIN
        ROLLBACK TRANSACTION;
    END

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT
        @ErrorMessage = 'dbo.usp_Element_Commit -- ' + ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

END CATCH;

When I run that and hit the RAISERROR inside the try section of the sproc I get the following multiple errors:

dbo.usp_Element_Commit -- Supplied Element Name (for new Element) already exists
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

What is the recommended way to handle errors and and jump to the catch block without messing up the external transaction?

Also if I remove the rollback from the catch block in the sproc then I get the same Transaction Count message with Previous Count = 1, current count = 2

Answers


I changed my sproc to follow this pattern of using either a save point or a transaction depending on if there is already an existing transaction. And it works as expected now.

BEGIN TRY
    declare @trancount int = @@trancount
    if @trancount = 0
        begin transaction
    else
        save transaction usp_element_commit_transaction;

    set transaction isolation level serializable

    -- Perform Name Uniqueness check (for new)
    IF EXISTS ( SELECT PK.identifier --... )
    BEGIN

        RAISERROR(60000, 16, 1, 'dbo.usp_Element_Commit', 'Supplied Element Name (for new Element) already exists')

        RETURN

    END

    -- Do stuff         

    COMMIT TRANSACTION
END TRY
BEGIN CATCH

    declare @xstate int = xact_state()
    if @xstate = -1
        rollback
    else if @xstate = 1 and @trancount = 0
        rollback
    else if @xstate = 1 and @trancount > 0
        rollback transaction usp_element_commit_transaction;

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT
        @ErrorMessage = 'dbo.usp_Element_Commit -- ' + ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

END CATCH;

Need Your Help

What else do I need to make kendo-grid's customm toolbar command work?

jquery kendo-ui kendo-grid

I need to add a custom toolbar command to my kendo-grid so I searched the kendo-ui documentation about the grid#configuration-toolbar where I found that:

Using DrawUserPrimitive and transforming that with some matrices

c# matrix xna primitive

Here is what I have: a custom DrawableGameComponent, containing children in which I call some DrawUserPrimitve. Each of the child can contain children etc.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.