As shadowfiend notes, fixtures are a common approach here.
However, you could also use a mock. What you're really testing here isn't the connection to the database or even that the database contains certain data, so you can rule that out of the equation. What you're testing is that some service (let's just say a 'method' for simplicity's sake) turns an input into an output in some particular way.
What you do, then, is mock the database connection for a particular test case so it returns a guaranteed result to whatever's doing the request in your method. You can then test that the method converts that input into the correct result. You've now unit tested the method rather than the entire service (in a nutshell - it can be more complex than that).
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.
However, you could also use a mock. What you're really testing here isn't the connection to the database or even that the database contains certain data, so you can rule that out of the equation. What you're testing is that some service (let's just say a 'method' for simplicity's sake) turns an input into an output in some particular way.
What you do, then, is mock the database connection for a particular test case so it returns a guaranteed result to whatever's doing the request in your method. You can then test that the method converts that input into the correct result. You've now unit tested the method rather than the entire service (in a nutshell - it can be more complex than that).