Using transaction rolled back for testing

I just discovered the idea of testing a stored proc by calling it from within a BEGIN TRAN t1 ROLLBACK TRAN t1 pair. I am a bit afraid of this. Is that a common practice ? Is it reliable ? My goal here is to quicly test a stored proc that reads and updates 2 databases (same server). The SP does not do any truncate but uses a table variable combined with an INSERT.. OUTPUT statement. The volume will be low (less than 1000 lines affected). Thanks


There are a few things that can go wrong:

  • The proc could do its own transaction management
  • It could execute non-transactable statements like CREATE DATABASE
  • It could have an error, causing the transaction to automatically rollback. If the proc then continues to run in some way, it might write stuff outside of a transaction
  • XACT_ABORT might be used inconsistently, causing the previously mentioned effect

In general, this is a good technique, though.

Truncate is transacted, btw.

Need Your Help

Jquery Internet Explorer 8 compatibility issue, does not load data unless history is deleted


Hey guys, I have a weird problem. I have an update system that refreshes data on a time interval. It works well in all browsers except internet explorer 8. The problem is that once it loads the dat...