Transaction is rolled back after commit?

Question

I'm experiencing some problems that look a LOT like a transaction in a stored procedure has been rolled back, even though I'm fairly certain that it was committed, since the output variable isn't set until after the commit, and the user gets the value of the output variable (I know, because they print it out and I also set up a log table where i input the value of the output variable). In theory someone COULD manually delete and update the data such that it would look like a rollback, but it is extremely unlikely.

So, I'm hoping someone can spot some kind of structural mistake in my stored procedure. Meet BOB:

CREATE procedure [dbo].[BOB] (@output_id int OUTPUT, @output_msg varchar(255) OUTPUT)
as
BEGIN
SET NOCOUNT ON 
DECLARE @id int
DECLARE @record_id int

SET @output_id = 1

-- some preliminary if-statements that doesn't alter any data, but might do a RETURN

SET XACT_ABORT ON
BEGIN TRANSACTION

BEGIN TRY
    --insert into table A

    SET @id = SCOPE_IDENTITY()

    --update table B

    DECLARE csr cursor local FOR 
        SELECT [some stuff] and record_id
        FROM temp_table_that_is_not_actually_a_temporary_table
    open csr

    fetch next from csr into [some variables], @record_id
    while @@fetch_status=0
    begin   
        --check type of item + if valid
        IF (something)
        BEGIN
            SET SOME VARIABLE
        END
        ELSE
        BEGIN
            ROLLBACK TRANSACTION
            SET @output_msg = 'item does not exist'
            SET @output_id = 0
            RETURN
        END

        --update table C

        --update table D

        --insert into table E

        --execute some other stored procedure (without transactions)

        if (something)
        begin
            --insert into table F

            --update table C again      
        end

        DELETE FROM temp_table_that_is_not_actually_a_temporary_table WHERE record_id=@record_id

        fetch next from csr into [some variables], @record_id
    end
    close csr
    deallocate csr

    COMMIT TRANSACTION
    SET @output_msg = 'ok'
    SET @output_id = @id
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
    SET @output_msg = 'transaction failed !'
    SET @output_id = 0
    INSERT INTO errors (record_time, sp_name, sp_msg, error_msg)
    VALUES (getdate(), 'BOB', @output_msg, error_message())
END CATCH

RETURN
END

I know, my user gets an @output_id that is the SCOPE_IDENTITY() and he also gets an @output_msg that says 'ok'. Is there ANY way he can get those outputs without the transaction getting committed?

Thank you.

Answer 1

You know the problem is that transaction dose NOT support rollback on variables because there is no data change inside database. Either commit or rollback of the transactions ONLY make difference on those database objects (tables, temp table, etc.), NOT THE VARIABLES (including table variables).

--EDIT

declare @v1 int = 0, @v2 int = 0, @v3 int = 0

set @v2 = 1

begin tran
set @v1 = 1
commit tran

begin tran
set @v3 = 1
rollback tran

select @v1 as v1, @v2 as v2, @v3 as v3

RESULT is as follows

Answer 2

Personally I never used transactions in stored procedures, especially when they are used simultaniously by many people. I seriously avoid cursors as well.

I think I would go with passing the involved rows of temp_table_that_is_not_actually_a_temporary_table into a real temp table and then go with an if statement for all rows together. That's so simple in tsql:

select (data) into #temp from (normal_table) where (conditions).

What's the point of checking each row, doing the job and then rollback the whole thing if say the last row doesn't meet the condition? Do the check for all of them at once, do the job for all of them at once. That's what sql is all about.