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?