For what it’s worth, we implement tests in the client language (C# in our case) and just call the stored procedures/views (in SQL Server) as any client would. Most tests are written so that they rollback after execution, and to tolerate any residual data that might have been left after the tests that must commit. A very small number of tests require a clean database, which we accomplish by traversing INFORMATION_SCHEMA and just emptying all the tables (we also temporarily disable constraints and triggers).

These tests are just normal “unit” tests from the perspective of Visual Studio, which brings one enormous benefit: you can put a breakpoint in T-SQL code and just right-click on the test and choose Debug, and the breakpoint will be hit! That’s possible because Visual Studio debugger can switch from C# to T-SQL seamlessly.

This is a fantastic way to debug in exactly the right test case which exposes the bug or behavior that you want to test.

Testing code is exactly where I'm doing the print debugging from. I'm sure not doing it on production unless I have no choice!

I don't take a dogmatic view of testing. There is no requirement that test code in X must be run in a test harness in X. There is no problem testing my database procedures with code being driven from my primary app language. What matters is that cases are covered, and that the desired outcomes are verified. My database code is perfectly well covered with testing. If yours isn't, that's a process problem on your end and is your problem, not mine.