CODEWITHSUNDEEP
sql-servert-sqltransactionssql-server-2012rollback
Konrad Z.
Konrad Z.

Reputation: 1652

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?

Upvotes: 1

Views: 4813

Answers (2)

Daniel B
Daniel B

Reputation: 797

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.

Upvotes: 2

Mathese F
Mathese F

Reputation: 559

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

Upvotes: 2

Related Questions