CODEWITHSUNDEEP
sql-servert-sql
Thej
Thej

Reputation: 375

How to handle nested transaction in stored procedure

I have code like below, now I need to commit only inner transactions and outer transaction may commit or roll-back. How to handle it?

    BEGIN TRY
        BEGIN TRANSACTION

        INSERT TABLE T1 (1,2,3)


            ----
            ----
            ----

        IF t1 > 10
        BEGIN
            BEGIN TRANSACTION
            INSERT INTO ERROR_LOG  (XX)
            COMMIT 
            return 1
        END 
    COMMIT 
    END TRY 
    BEGIN CATCH
            ROLLBACK

    END CATCH

Only when t1 > 10 then transaction should commit the error log and terminates program

Upvotes: 1

Views: 600

Answers (1)

James Z
James Z

Reputation: 12318

If you're hoping to store some results into ERROR_LOG and rollback other changes, the inner transaction is not going to do that, since in SQL Server the outer transaction will rollback everything.

Here's some testing & explanation done by Paul Randal: http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/

One way to get around of this limitation is to use table variable for the logging, since it will not be rolled back, and then insert the results into the log after doing the rollback.

Upvotes: 2

Related Questions