Transaction wrappers and SPs

Question

Transaction wrappers and SPs

JKSQL

SSCrazy Eights

Points: 8323
More actions
January 26, 2010 at 2:41 pm

#140914

I have a procedure that calls other procedures. If the first two calls to playground and sales work, but an error is thrown in deliveries does the whole SP roll back? Meaning would the delete still run on playground and Sales? Every SP being called also has a transaction.
BEGIN TRANSACTION
EXECUTE DEL_PlayGround @Id= @Id
SET @err = @@ERROR
IF @Err <> 0 GOTO ERRORCODE
EXECUTE DEL_sales @Id= @Id
SET @err = @@ERROR
IF @Err <> 0 GOTO ERRORCODE
EXECUTE DEL_Deliveries @Id= @Id
SET @err = @@ERROR
IF @Err <> 0 GOTO ERRORCODE
COMMIT TRANSACTION
GOTO RETURNCODE
ERRORCODE:
BEGIN
ROLLBACK TRANSACTION
END
RETURNCODE:
RETURN @Err

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MonkeyK Old Hand Points: 388 More actions · Answer 1

Books Online: Nested Transactions

Committing inner transactions is ignored by Microsoft® SQL Server™. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.

JKSQL SSCrazy Eights Points: 8323 More actions · Answer 2

Thats funny because we proved that wrong. If we do not check for errors it is not rolling back. We now are checking so it does roll back. I will have to look at how they recommend calling nested SPs

MonkeyK Old Hand Points: 388 More actions · Answer 3

Sorry, I didn't read your original post well enough. You were asking if the internal rollback would cause the external transaction to rollback as well. As you have seen, the answer to this is no. This is a good thing because your external transaction might be able to take corrective action based on the internal transaction failure.

Also, rather than checking @@ERROR, you may want to look into RAISEERROR and the TRY/CATCH syntax available in SQL Server 2005.

JKSQL SSCrazy Eights Points: 8323 More actions · Answer 4

Agreed I should have used raise error when I started making these but the code architecture expects the @@error return so I screwed that up. I have over 200 procs I would need to change. Thanks for the help.

Lamprey13 SSCertifiable Points: 6190 More actions · Answer 5

Not to try to confuse things anymore, but there is also: SET XACT_ABORT { ON | OFF }

Which may or maynot help you in your particular scenario.. 🙂

JKSQL SSCrazy Eights Points: 8323 More actions · Answer 6

that is a good idea. I did not think about using that since I had the wrapper on that already. I wonder if that will work. I will test and tell you.