I'm currently testing a Web API that delivers JSON content that I am validating the results for with defined JSON files. This works well. We know what the request is, can develop a query that should give us similar results, and verify against those results within a JSON structure. All well and good.

This started a discussion on whether or not we test the underlying Sprocs that feed into the API. In general the API does some dynamic query building that basically queries a set of stored procedures that run against a schema I will call CustData. We do have tests that check for data insertion, as well as the stored procedures that populate all the tables in the CustData schema.

I can see where its necessary to test the stored procedures that return the data from CustData, but since we know what's in the table already and the WebAPI basically delivers the data the point was made that we would be testing the stored procedures twice. Especially since the stored procedures list is intended to grow, its a question of do we spend the time on the stored procedures and the Web APIs, doing double testing (which is ok in my opinion if we can get the time for it) or do we treat the middle like a black box and just deal with the data insertion and the Web APIs (which also meets some task requirements).

Anyone in a similar environment able to throw some examples on this scenario?

2 Answers 2


Without knowing how complicated your stored procedures and Web APIs are, it is hard to know whether my environment is similar to yours.

Your tests serve two purposes: to find bugs, and to narrow down where the bug might be. If someone changes the Web APIs to use the stored procedures in a new way, and the change is buggy, it may be unclear whether the problem is in the stored procedures or the Web API. You might ask yourself how you would go about narrowing down a problem in that situation. If you believe your current tests would suffice, you should not spend time writing more. If you aren't sure, you might want to unit-test the stored procedures separately.

Your choices are not black and white. You could unit-test stored procedures that are complicated or non-obvious. You could choose to not unt-test stored procedures that are simple and obvious.

  • Yes, this is sort of my dilemma. Even if the sprocs were simple, it is still a matter of are they called correctly from the Web API. I am of the mindset that with automation its cheap to add a test at the DB layer and for the API. Though if the sprocs grow and change that becomes a maintenance cost. I'm trying to strike the right balance here, and not sure where that is yet.
    – MichaelF
    Commented Jun 27, 2016 at 11:20

I'd say it depends on what sort of testing you're doing, and how much testing of the sprocs will have been done by the time they get to you. If you think of the system in terms of a program, testing the sprocs is similar to unit testing; they're the simplest thing that can be called. So you would generally mock up calling the sprocs in one case, and in the other case, you'd mock up the things that called the sprocs. If you're only using the web API to test the sprocs, you may not be able to test limits and the like appropriately.

You also need to think about how much control you have over who calls the sprocs; yes, from your point of view, the only thing using them will be the web API, but I can think of a number of examples where interfaces that were intended to be used for only one thing were used for something else, but the new exploiter didn't understand the assumption that had been made when the system was being designed (i.e., caching certain results was being done at the web server level, so when someone developing a mobile application decided it would be faster to go straight to the database, they brought a fairly large system to its knees by calling a computationally intensive stored procedure much, much more frequently than it was designed to have been called.)

If there's a great pressure to get the system deployed, and not much time/resources available (often the case), I'd would focus on the behavior of the system, working under the assumption that the designers understood how the system was going to be used (which can be risky), but with the knowledge that in doing so, I'm accumulating technical debt. Given the choice, though, I'd spend more time with the system components in isolation, before testing the system as a whole. The earlier you can find bugs, the better, and the closer to the source of the bug you can get, the better.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.