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.