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?
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.