1

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

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • 2
    I do this frequently, and have never had a problem with it. – JohnD Jan 23 '14 at 14:14
  • @JohnD: Thanks. I just "discouvered" this, looks like a nice timesaver – iDevlop Jan 23 '14 at 14:18
  • As far as it goes, yes. It just shows that the stored procedures don't throw errors. It doesn't show that they do the right thing. If they did nothing at all they'd pass these tests. – simon at rcl Jan 23 '14 at 15:16

1 Answers1

2

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.

Community
  • 1
  • 1
usr
  • 168,620
  • 35
  • 240
  • 369
  • 1
    upvoted and +1 for the truncate because there's a myth truncate table cannot be rolled back – jean Jan 23 '14 at 15:36
  • @jean that's why I said it. I don't know where that myth is coming from. DROP TABLE is also transacted. All TRUNCATE does is set the b-tree root to an empty page and deallocate the rest on commit. The b-tree root can be rolled back easily. – usr Jan 23 '14 at 15:40