>Care to elaborate a bit? Especially, why do you think unit testing and debugging of SQL is not hard?
An SQL returns results directly in tables which you can check in all kinds of ways. You can create any number of temporary tables, with the same schema as your business tables, and check all kinds of invariants.
There's absolutely no reason why unit testing SQL should be harder than anything else, considering a single query as the "unit" of testing.
In fact, that's to the built-in checks, constraints and types a RDBMS has, you are freed from having to unit test all kinds of crap too (similar to having less to unit test in Haskell vs Ruby).
Well, you'll have to store your mock data somewhere, which in this case means more databases, often on other servers; so you'll have your SPs connecting to another DB in order to access their data.
It's not very hard, it's just impractical. Mainly because a database is a giant bag of statefulness (to put it scientifically).
You need to prepare test data, you need to update and maintain the test data. That is already a big barrier to entry.
The actual testing involves three simple steps: setting the initial state of the database, run your queries/procs, verify the results. This will be unbearably slow even for a small test set. So you start to make things complicated by trying to be smart, like only revert the state you modified, or using SQLite for tests and Postgres for production, or by running the database server on a RAM filesystem, etc, etc.
I've seen a few people go down the rabbit hole and noone came up with a solution I could be happy with.
I don't see that writing code to test SPROCs is that hard just have a set of inputs that match all of the use cases including all the edge ones run that through and check that the results on the db are as expected.
Debugging mm possibly slightly harder in that you might have to have a 3rd monitor for Toad or work Manager - but you code your sprocs properly in the first place you should not have that many problems that jump between code and sql .
Just saying its hard doesn't help in that case we ought to still be coding in GWBASIC
