SQL Code Guard and nested transactions rollback?

Asked Jul 01 '16 at 13:31

Active Jun 13 '17 at 20:54

Viewed 52 times

I have a stored procedure that may be called from a transaction. When there is an exception I want to rollback the transaction (the top-level transaction). I would like to use something like this:

...procedure definition...
BEGIN

    BEGIN TRY

        --work&logic

    END TRY

    BEGIN CATCH 
        -- An error occurred!
        IF @@TRANCOUNT > 0
            ROLLBACK
    END CATCH
END

The catch with this is, that the SQL Code Guard reports for this the warning EI020 - ROLLBACK TRANSACTION without BEGIN TRANSACTION. Is there a way to write this properly?

edited Jun 13 '17 at 20:54

David Atkinson

5,759
2
28
35

asked Jul 01 '16 at 13:31

user992990

You pass an error flag back to the calling transaction and roll back from there. – OTTA Jul 01 '16 at 13:35
Where have you put BEGIN TRANSACTION command? – Joe Taras Jul 01 '16 at 13:37
In the caller procedure. It can also be called from the c# code with 'using TransactionScope'. – user992990 Jul 01 '16 at 13:43
So basically, I should not need the try-catch in the procedure that is being called at all, but rather in the caller procedure/query, right? – user992990 Jul 01 '16 at 13:53

SQL Code Guard and nested transactions rollback?

0 Answers0