XACT_ABORT within TransactionScope for simple error handling

Question

This question is an attempt to explore the semantics involved in the interaction between TransactionScope and xact_abort in SQL Server 2000.

If the following sql is executed within a TransactionScope, and the first delete command errors, will the second delete command be run? (Assume a foreign key from parent to child in order to ensure failure.)

create procedure test
    @id int
as
set xact_abort on
-- no explicit transaction is created

-- if this fails
delete from dbo.[parentTable]
where id = @id

-- will this run?
delete from dbo.[childTable]
where id = @id

Assuming trivial application code as below:

public bool TryTestStoredProcedure()
{
    try 
    {
        using (TransactionScope t = new TransactionScope())
        {
            MethodThatRunsTestStoredProcedure();
            t.Complete();
            return true;
        }
    }
    catch
    {
        return false;
    }
}

What will be the return value from this method if the first delete statement in the stored procedure fails? What about if the second delete statement fails?

Answer 1

After doing some testing based on my own pseudo-test-code above, it looks like the only difference between using XACTABORT and not within a TransactionScope is that if the proc uses XACTABORT, it fails sooner (for the errors that XACTABORT catches) than if the proc does not use XACTABORT. The TransactionScope appears to catch exceptions that are raised at any point during execution within its scope, even if other operations have occurred after those exceptions were raised.

For the stored procedure with XACTABORT on, no changes were seen after the first failure, and when XACTABORT was off, changes from the second statement were observed. However, in both cases the TransactionScope did not Complete, but threw an exception.

So the quick answers to my questions are:

• If XACTABORT is on, the second delete statement will not be run
• If XACTABORT is off, the second delete statement (and any subsequent code) will be run
• If an exception is encountered during execution of the stored procedure, the return value for the method will be false