Have to agree with the others...

Whilst I understand that you use this pattern as a way of debugging and inspecting what your procs do, I think good commenting, some debug prints (commented out for production) and the inherent problems with

* formatting

* ease of maintenance

* potential problems with SQL injection (although I see you've used quotename)

* performance

* ..... etc

outweigh any benefits.

Having said that, I definitely appreciate you writing the article and getting to see different approaches to how people work.

You should also be aware that if you wished to have a break in your strings you can do something like

set @sql = 'this is the first line

and this is the second line'

without needing cumbersome @crlf variables, plus signs, extra quotes, etc.

Your runmode 0 can be done without dynamic SQL as you need simply include

where @runMode <> 0

at the end of your select clauses (or use and @runMode <> 0 if you already have a where clause)

runmode 1, where you show the query text, is probably best accomplished by just examining the stored procedure with syntax highlighting, etc.

runmode 2 is obviously where you want to execute and return data - the case above for mode 0 takes care of this.