Deborah Melkin does not take kindly to bugs:
I often find that I have to write complicated stored procedures where I need to check things as I go along. My go-to for using this snippet is when I write stored procedures that use dynamic SQL. You’d be surprised (or not) at how often I have had to do this over the years. There’s been functionality where the user gets to choose the columns being used, rewriting ORM data layer “catch-all” queries to improve performance, and cross database queries where the name of the database may not be the standard name (think development and QA databases living on the same SQL instance.)
Click through for an example of where the @Debug parameter pays off. My recollection was that, for really long NVARCHAR(MAX) strings, running PRINT by itself might cut off the code after ~4000 characters, but that could be a historical recollection.
Thanks for including this post! I think “Deborah Melkin does not take kindly to bugs” is one of the nicest things people have said about me… 🙂
You are correct – PRINT is limited to 4000 characters. What I have done in the cases where I know the print statement will be much larger than that limit is split up the statement into multiple statements where I do substrings of the variable along the lines of:
PRINT substring(@sql, 1, 4000)
PRINT substring(@sql, 4001, 4000)
PRINT substring(@sql, 8001, 4000)
I usually copy and paste the code into another query window so I can fix any weird breaks in the code from that sort of split before I review and test the query itself. But this is just one option that I’ve used.