I mean, you can also use CTEs for that but it is of course limited.
For all of the flaws of managing SQL, it's when I get handed giant swathes of dynamic SQL that I die a little inside.
I've just been reading up on CTEs - thanks. That's the kind of thing I thought would be out there.
Well that's why (in my opinion) you should _generate_ the queries, not _write_ them. Use an ORM or whatever flavor of basically a SQL generator you prefer. Or even easier, use any programming language to concatenate around some strings and build your SQL.
The power of using SQL comes from the fact that, no matter what, it's always there. You can always write/buy/find another client for your DB in another language, and as long as it generates SQL, it's all good.
You should be able to abstract logic out to blocks for the duration of the query. Along these lines:
cset would be scoped to your connection.Instead of pages of copy-and-paste queries where the levels are all mixed up with one another. You can get something similar by polluting the namespace (e.g. views, temporary tables) but that's hacky and not the same as good.