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 Transaction
    Set @MyTran = 1
At the bottom of your proc, to rollback or commit:
If @MyTran = 1 and @@Trancount > 0
          Rollback Transaction -- Or Commit Transaction

No comments:

Post a Comment

Post Your Comment...