I've got a development scenario where stored procedures (sprocs) will output diagnostic/debug messages to the SSMS console during execution via a common dbo.DBPrint sproc. As some sprocs may call other sprocs it would be nice, for readability, to indent, in dbo.DBPrint, the diagnostic/debug messages according to the call nesting level. The problem I'm running into can be elucidated by running the following code and observing its unexpected, yet documented, results:
Exec sp_ExecuteSQL N'Print @@NestLevel;';
/* Output results
I've even tried running the sp_ExecuteSQL N'Print @@NestLevel'
by itself to see if the Exec
was generating the extra nesting level; nope... sp_ExecuteSQL is just being difficult...
Ideally, the dbo.DBPrint code would somehow need to "know" that it was running inside an sp_ExecuteSQL context so as not to double-indent its output relative to the previous calling level's output. Call-level indentation works fine with scripts & sprocs that don't use sp_ExecuteSQL (i.e., Exec(...) only generates the expected additional nesting level).
Most of you will correctly state that "this ain't that big of a deal
" and I'd logically say that you are correct, however, this has turned out to be one of those things that is "keeping me up at night" so to speak, for
any nasty kludge I can find to get it to work!
solution you guys can come up with must work entirely from within the dbo.DBPrint sproc, i.e., you can't pass anything in to give it a hint as its not always possible to tell what environment its being called from (so you'd know to pass the hint in).
Baring a solution I only hope that my there's-gotta-be-a-kudgy-T-SQL-trick
insomnia isn't contagious...
PeteKI have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.