Improve support for testing procs using transaction handling and TRY/CATCH

[davemurp]

There are many times when it makes sense to use XACT_ABORT ON and TRY/CATCH blocks for error handling but unfortunately this makes the code very difficult to test using tSQLt. This has been mentioned several times on stack overflow with this being one example.

The proposed solution (by Peter Pundy) which in tSQLt.Private_RunTest which adds bit more logic to the transaction handling appears to do the trick. This support for transactions would be a very welcome addition if it could be added.

IF XACT_STATE() = 1 -- transaction is active
    ROLLBACK TRAN @TranName; -- execute original code
ELSE IF XACT_STATE() = -1 -- transaction is doomed; cannot be partially rolled back
    ROLLBACK;   -- fully roll back
IF (@@TRANCOUNT = 0)
    BEGIN TRAN; -- restart transaction to fulfill expectations below```

[liamgjohannes]

Bump.
I think this should be looked at more urgently, as using transactions within stored procedures is not uncommon. The tSQLt.Private_RunTest does not account for executing a proc with a transaction and falls over itself when it needs to be rolled back.