Transactions

• 
  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/
  

  sam.blackburn May 17, 2018 15:44

  - 0 +
• 
  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.

  Not_Happy May 17, 2018 15:47

  - 0 +
• 
  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.

  AlexYates May 18, 2018 07:33

  - 0 +
• 
  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

  Not_Happy May 18, 2018 08:01

  - 0 +
• 
  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. 😉

  AlexYates May 18, 2018 10:14

  - 0 +

Add comment

Please sign in to leave a comment.