January 26, 2010 at 2:41 pm
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
January 28, 2010 at 9:45 am
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.
January 28, 2010 at 10:03 am
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
January 28, 2010 at 11:08 am
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.
January 28, 2010 at 12:19 pm
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.
January 28, 2010 at 12:46 pm
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.. 🙂
January 28, 2010 at 1:27 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply