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

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