I've been considering this for quite a long time now.
The basic question is: how to unit test stored procedures?
I see that I can set up unit tests relatively easily for functions in the classic sense (I mean they get zero or more arguments and return a value). But if I consider a real-life example of a seemingly simple procedure inserting a row somewhere, with a few triggers doing this and that before or after the insert, even defining the boundaries of a 'unit' is quite difficult. Should I test only the
INSERT itself? That's fairly straightforward, I think–with relatively low value. Should I test the result of the whole chain of events? Apart from the question whether this is a unit test or not, designing a suitable test can be quite a strenuous job with lots of additional question marks arising on the way.
And then comes the problem of constantly changing data. In the case of an
UPDATE affecting more than just a few rows, every potentially affected row must be included somehow in the test cases. Further difficulties with
DELETEs and so on and so on.
So how do you unit test your stored procedures? Is there a treshold in complexity where it gets completely hopeless? What resources are needed for maintenance?
EDIT One more small question, based on AlexKuznetsov's answer: Or is there a treshold under which it is completely useless?