Good approaches here have some form of higher level validation and error messages, such that by the time the SQL engine gets your query, it runs well almost all the time.
Good approaches here have some form of higher level validation and error messages, such that by the time the SQL engine gets your query, it runs well almost all the time.
Then you try to bite the sandwich, and half the ingredients go sideways: change one parameter, and the generated SQL can look wildly different, etc.
I still think DSLs are superior to macros though; a smart DSL transformer will catch a whole class of errors, while a macro engine like M4 could happily spit out garbage with syntax errors.
But it is perfectly possible to write correct SQL code. Just like it is possible to solve mathematical equations with pen and paper.
I try to accept SQL as it is and avoid any abstractions because they make the query impossible to read.
I write my queries top down using CTEs and test manually along the way.
And it turns out that 90% of the problems are not in the query logic but with the input data and the two villains of tabular data.
Duplicate rows and NULL values.
And then write sql statements for everything else.