It's hard to tell without asking the OP for specifics, but..
"I was recently asked to add a test suite to a service I wrote that is basically a simple wrapper that returns the result of a stored procedure call."
I interpreted this as meaning that the unit test would be for the 'service' and whatever it does with the stored procedure's result (and the arguments passed in the first place) rather than on the stored procedure itself. The reason for this interpretation was how he considered passing null values in order to exact a null response to be an acceptable test. Such a 'wrapper' might be a presenter system or simply convert data from the result to a different form, these things could be unit tested by mocking what the database returns.
If, however, he meant that the operation of the stored procedure was to be tested, then my previous post was moot.
That's a great question. It wasn't specified to me either, so I don't even know. But even verifying that the stored procedure actually gets called is problematic. In practice, one of the problems that actually occurred with the service is that the service host somehow lost permission to exec the stored procedure. That's a server admin issue.
In my experience it feels like that case is pretty representative of most system defects, in that the majority of them seem to fall outside the space of defects that are feasible to test. I've always assumed I'm doing something wrong, given how many supporters unit testing has.
"I was recently asked to add a test suite to a service I wrote that is basically a simple wrapper that returns the result of a stored procedure call."
I interpreted this as meaning that the unit test would be for the 'service' and whatever it does with the stored procedure's result (and the arguments passed in the first place) rather than on the stored procedure itself. The reason for this interpretation was how he considered passing null values in order to exact a null response to be an acceptable test. Such a 'wrapper' might be a presenter system or simply convert data from the result to a different form, these things could be unit tested by mocking what the database returns.
If, however, he meant that the operation of the stored procedure was to be tested, then my previous post was moot.