I'm writing a .NET 7 app that connects to an Azure SQL database using ADO.NET.

Some code paths require a transaction to be opened and multiple commands (calls to stored procedures) to be sent to the database as part of the same transaction.

The stored procedures are mostly simple, atomic CRUD operations, but they may also involve multiple statements if the app entities and their underlying storage are different (e.g., for performance or abstraction).

My current need is to have the stored procedures return some value to the app layer to signal some partially faulted but recoverable state (e.g., an UPDATE on a row that does not exist).

I also may want the stored procedure to roll back the transaction to an earlier save point in these cases, but avoid it to roll back the entire transaction.

The app will manage these states using custom exceptions (unexpected, recoverable errors), so my first approach was to use THROW in my stored procedures as well since the exception would map nicely with ExecuteNonQuery() and with some care also with ExecuteReader().

The problem is that in my stored procedures, I use XACT_ABORT ON and TRY...CATCH by default, so if I throw a custom error from inside the TRY block, the transaction would be rolled back at the end of the batch.

I thought about using RAISERROR instead of THROW (that should not honor XACT_ABORT as per Microsoft documentation). Still, on an Azure SQL database, I cannot use sp_addmessage, so I'm limited to returning only error 50000; plus Microsoft suggests THROW for new developments.

Also, I don't think I want to set XACT_ABORT OFF because if a non-custom exception is thrown, I'd like the entire transaction to be rolled back since it's actually an unexpected and non-recoverable situation.

The only options I see left are a custom OUTPUT parameter (e.g., @State) or the RETURN value, but that would require me to ensure the output/return value is always mapped inside the app code, instead of throwing by default.

Are these considerations correct? Are there any alternatives that I'm not considering?

I also wrote this draft stored procedure template for this situation using the return value. Is there anything I should pay attention to?

create or alter procedure [App].[SampleProcedure]
as
declare
    @savepointName char(32),
    @shouldCommit bit;
begin
    set xact_abort, nocount on;
    set transaction isolation level read committed;

    set @savepointName = replace(newid(), '-', '');

    if @@trancount > 0
    begin
        set @shouldCommit = 0;
        save transaction @savepointName;
    end;
    else
    begin
        set @shoudlcommit = 1;
        begin transaction @savepointName;
    end;

    begin try
        if /* some condition */ 1 = 0
        begin
            rollback transaction @savepointName;
            return -1; /* Precondition failed */
        end;

        -- update set ... where ...;

        if /* some condition */ 1 = 0
        begin
            rollback transaction @savepointName;
            return -2; /* Condition failed */
        end;

        -- insert into ... values ...;

        if isnull(@shouldCommit, 0) = 1
        begin
            commit transaction @savepointName;
            return 0; /* Everything is fine */
        end;

    end try
    begin catch
        if @@trancount > 0
        begin
            rollback transaction;
        end;

        throw;
    end catch
end;

CodePudding user response：

After some time, I came to these conclusions:

• "Recoverable errors" are not exceptions. It would certainly be convenient to treat them as such, but the fact that you can detect and recover from them means they're just rare error conditions you thought about.
• More than C# exceptions, T-SQL ones are targeted to exceptional, non-recoverable situations where the transaction needs to be rolled back. The best practice of setting XACT_ABORT ON inside procedures is proof.
• As per the previous two points, a return value (or an output parameter) is the correct solution to the problem. I chose the return value because it's enough for my use case and will allow me to keep the EnableOptimizedParameterBinding enabled.