Conditional transaction rollback

问题:

I am wondering, if there is a possibility to apply conditional transaction rollback based on state of particular batch. For example, I have a following code:

BEGIN TRAN 
--EXEC No 1
    EXEC [dbo].[MyProc] 1;
    GO
--EXEC No 2
    EXEC [dbo].[MyProc] 22;
    GO
--EXEC No 3
    EXEC [dbo].[MyProc] 333;
    GO
--EXEC No 4
    EXEC [dbo].[MyProc] 5;
    GO
COMMIT

And I want to rollback entire transaction if the EXEC No 3 fails. If any other execution fails I want SQL Server to continue executing my query. Is it possible?

回答1:

Begin tran
Begin try
.....
commit
End try
begin catch
rollback
End catch

http://msdn.microsoft.com/fr-fr/library/ms175976.aspx

They prefer to do the begin tran and commit outside

回答2:

Yes, this is possible. Use TRY/CATCH blocks around each procedure call, determine how to handle errors for each procedure in each respective CATCH block.

In your case, only perform a ROLLBACK in the CATCH block for the third procedure call.