will my Stored procedure rollback far enough?

Question

I am working with a sql server 2008 database through php with an odbc connection.

I am reading data files and logging them into the database but due to varied file sizes/layouts, my sql is somewhat automatically generated.

The sql is called in this order:

set autocommit to off
execute some sql 
execute more sql 
execute a SP.
commit

in my stored procedure I want to do error handling with a try catch like so:

BEGIN try
    --sql
END try
BEGIN catch
    rollback
END catch

I am wondering will this roll back only the sp and leave my other sql to commit or will it go back to the point where autocommit was set to off?

another possible solution would be to return a false/true from the stored procedure and use that to call a rollback from php. is this possible? if so, how does one return a value with a stored procedure?

You are looking at nested transactions. See this thread: http://stackoverflow.com/questions/2073737/nested-stored-procedures-containing-try-catch-rollback-pattern/2074139#2074139 — gbn, Aug 05 '11 at 17:16

score 1 · Answer 1 · edited May 23 '17 at 12:18

AFAIK, provided you wrap everything that you wish to rollback into a transaction then you know that you can rollback that far. Beware of your called procs COMMITting their own transactions, however - see here for more.

For example, using what you provided:

DECLARE @success bit
BEGIN TRAN T1

    BEGIN try
        EXEC [dbo].[usp_MyProc]
        SET @success = 1
        PRINT 'SUCCESS'
    END try
    BEGIN catch
        SET @success = 0
        PRINT 'FAIL'
    END catch

    IF @success = 1
        BEGIN
            COMMIT TRAN T1
        END
    ELSE
        BEGIN
            ROLLBACK TRAN T1
        END
PRINT @@TRANCOUNT

should end up with a TRANCOUNT of 0 in both cases.

This will break with nesting though: that is, if a ROLLBACK happens in a procedure or trigger. — gbn, Aug 05 '11 at 17:17

score 0 · Answer 2 · edited May 23 '17 at 09:58

0

You are looking at nested transactions.

See this thread: too much to copy/paste sorry

Nested stored procedures containing TRY CATCH ROLLBACK pattern?

edited May 23 '17 at 09:58

Community

1
1

answered Aug 05 '11 at 17:17

gbn

422,506
82
585
676

will my Stored procedure rollback far enough?

2 Answers2