Thanks for the straight-forward example, Steve. Things like this show that tSQLt testing isn't difficult and helps you avoid deploying bugs in the first place.
We adopted tSQLt for database unit testing as a formal standard over 4 years ago. The startup was a little difficult as with all mindset changes but we kept at it. We have developed an internal schema to augment the base tSQLt package to generate simple tests (schema and constraint, for example) and to release SCHEMABINDING. While some parts are code generators we also have an overload of FakeTable to incorporate our local enhancements.
We find that, many times, we are the first exposure new developers have with tSQLt specifically and database unit testing in general. I'm not sure what that says about how, as an industry, we are training our developers.
Calling a stored procedure instead of embedding the code certainly makes for easier maintenance. I think the larger benefit is training developers to think in terms of black boxes. Then the test scenario deals with input and expected output without the test knowing the how.
Unfortunately a robust unit test infrastructure and skillset does not keep developers from writing EF, effectively embedding code, giving other layers intimate knowledge of the data layer instead of operating on contracts, and creating code where part of it is never unit tested, only integration tested. Given the source of the unit testing mantra (which was NOT database people), you'd think that testability and reduced coupling would be enticing arguments.
Buy the ticket, take the ride. -- Hunter S. Thompson