This is also often the problem with disaster SQL queries. I've seen some monsters that got hopelessly tangled up in their own JOIN constraints, trying to fetch all the data in one roundtrip because OMG LATENCY but then having to do full table scans over large-ish tables instead. Rewriting it as three small indexed queries reduced the runtime from 40 minutes (!) to less than a second.
Don't do too much in one operation whether it's regexes, SQL queries or OOP classes!
I’ve used the pattern several times of “select these SQL objects into a cursor, then iterate over the cursor to assign/revoke/check permissions on the objects”.
It’s still in a single batch of SQL (stored procedure in our case, so no additional network roundtrips), but the code is vastly clearer to read/maintain this way.
They're clearer in the sense that it makes it easier/possible to do things like:
While maintaining/changing the SQL, comment in/out select-statements-as-printf-debugging, and comment in/out actual execution of the statements themselves.
These cursors would often contain [identifying object reference], [category of statement], [text of SQL statement to execute]. You would write a select statement to populate the cursor, then loop over the cursor to run all the statements in the order you wanted (drops, then user/role creates, then grants, or whatever the situation called for).
It's not about logical clarity, but practical maintainability given the (overall weak) state of tooling for database queries. Is it a bastardization of SQL to do something that "should be" done in another scripting language? Maybe, but there's a lot of power in giving the DBAs tooling that works exclusively in a language and environment that's familiar for them rather than splitting it across SQL and python/tcl/ruby/whatever. Not nearly every competent [relational] DBA is competent across multiple languages. Every competent [relational] DBA is competent in SQL.
Is it even possible to use set-based SQL to call EXEC SQL EXECUTE IMMEDIATE or sp_executesql on each statement in a set?
Don't do too much in one operation whether it's regexes, SQL queries or OOP classes!