ROLLBACK FOR Store Procedure called From BizTalk || Implement Optional Transaction in StoreProcedure

Monday, November 12, 2012

A simple trick that you could try would be to test for an exisitance of a
transaction before starting a new one in the proc. But this would require
minor modifications to your stored procedure. For example:
 
At the top of the proc,
 

Declare @MyTran bit
 
-- If Caller Started the Transaction, let the caller handle it
-- otherwise control it in the proc.
If @@Trancount = 0
Begin
    Begin Transaction
    Set @MyTran = 1
End
 
At the bottom of your proc, to rollback or commit:
 
If @MyTran = 1 and @@Trancount > 0
 Begin
          Rollback Transaction -- Or Commit Transaction
 End
 

No comments:

Post a Comment

Post Your Comment...