Transactions

Not_Happy

I have just created a unit test that, because of the way our environment has been configured, drops a series of objects prior to generating the data/objects under test. In order to maintain the integrity of the database in case of issues, I am trying to wrap the text proc in a transaction so that I can at least tidy up in the event of a problem. Obviously, just wrapping the entire test in a transaction rolls the results back and so the test just reports that it succeeds. Technically, it does since everything has been rolled back, but it's also not actually testing anything. In such a case, success is not the correct answer.

What is the recommended method for including a transaction in the test?

sam.blackburn

I'm not sure what best practice is, but I can offer a solution that should work: table variables don't participate in transactions.  If you store your results in a table variable, you should still be able to copy them to the real results table after the transaction has been rolled back.

http://www.sqlservercentral.com/blogs/steve_jones/2010/09/21/table-variables-and-transactions/

Not_Happy

I'm not sure that will work. Doesn't the assert proc reference the temp tables used for the results by passing the names to Private_CompareTables? This wouldn't work with table variables.

AlexYates

Forgive me if I'm missing the point, but doesn't tSQLt handle this for you out of the box?

You shouldn't need to handle transactions within the test sproc itself because the tSQLt.run will roll back the transaction after executing the test anyway. That's why stuff like FakeTable is safe.

Not_Happy

The theory is that it will handle things, but based on experience with Redgate software, it often doesn't handle things at all well. we've had multiple occasions when we've had to have them supply us with urgent patches to fix things. We've only just started using SQL test and already I've discovered a number of things that don't work properly. Since we have a very large scale system I want to ensure that I am in complete control of what happens in the database

AlexYates

Not sure if it makes you feel more comfortable, but the transaction handling is managed by the open source tSQLt layer, not the Redgate layer.

So you can fork it and/or contribute your own patches if you prefer. 😉