Trouble printing out long VARCHAR(MAX) strings?

I've had to do lots of work recently generating very large dynamic SQL strings, and one of the biggest annoyances is that when you PRINT a VARCHAR(MAX)/NVARCHAR(MAX) string that is longer than 8,000/4,000 characters, SSMS only displays the first 8,000/4,000 characters. This makes it nearly impossible to work in SSMS when generating large SQL strings. I always like to print them out and then copy/paste them to a new window to see if they run, but you can't because of this limitation. I spoke to someone in Microsoft a while back about this and was told that this behavior is 'by design'.

I can't see how that holds water. The whole point of the (MAX) datatypes is that they support very large sizes. So not being able to print them out equals a bug as far as I'm concerned. However, I was able to find an entry in Connect about this (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=196643) and it looks like it was admitted as a bug in that entry. If you've had this problem and find it annoying, you can follow the link above and vote for the issue.

Now, here is the workaround. I created the attached SP that is able to print out all the characters in these long strings. Instead of doing a PRINT command on the string, call this SP and pass it the string as a variable. You will receive the full length of the string printed out in the output window in SSMS.

As for the details of how it works, here is the scenario:

1. Imagine you are generating a very long NVARCHAR(MAX) Dynamic SQL string, which is much longer than 4,000 characters. You'd like to print it out the Dynamic SQL string and see if it runs separately. However, when you do a PRINT command, you are surprised to see that it only prints out the first 4,000 characters. In other words, it truncates your string. And now the string is not runnable as an SQL Batch.
2. So, as a workaround, you realize that there is a simple solution: use multiple PRINT commands, and print out a 4,000 character chunk of the string with each PRINT statement. The result looks like the following, and it's messy but it appears to work:
   PRINT SUBSTRING(@SQL, 1, 4000)
   PRINT SUBSTRING(@SQL, 4001, 4000)
   PRINT SUBSTRING(@SQL, 8002, 4000)
   
3. There are several drawbacks to the above approach. First, you don't know exactly how long the string is going to be every time you run it, so you don't know how many of the above lines you have to type. And you know that if at some point you try to generate a string longer than the maximum size (12,000 characters in the above case), you will have to add more PRINT statements. However, since this is only used for debugging and not production, the fix for that is relatively easy...just add more PRINT statements when the error happens. The REAL problem is that whenever one of these 4,000-character chunks finishes printing and the other one starts, SSMS inserts a CR/LF character. If it so happens that this CR/LF chops off an SQL statement in mid-sentence, then you have non-working SQL. You will get a syntax error if you try to run it, and this means you have to go and remove these CR/LFs manually before running the SQL. This may not seem to be a big deal, but if you're dealing with a really long 50,000 or 100,000+ line string of Dynamic SQL code, it becomes a major nuisance. There are many CR/LFs to remove, and you have to do this EVERY time you regenerate the code. Very annoying.
4. Hence, the attached SP comes into play. Here's how it works: the first step it does is to figure out how long the string is and break it up into 4,000-character chunks, much as we did manually above. However, after this, it fixes the 'broken SQL line at the 4,000th character position' problem as follows:

• It detects if there is a broken line at the end of the string. This is as simple as assuming that the line is broken if there is a character at the exact 4,000th position. This may not always signify a problem, as a word may occasionally end on the 4,000th position, but we can discard that possibility as immaterial, since the fix for this does not otherwise break the SQL in this case.
• Once it detects that there is a broken line, it traverses the problematic 4,000-character chunk backwards from the end and stops printing this chunk on the last CR/LF before the line that contains the 4,000th character. The next chunk will then start at the point where this chunk stopped, and this gives you working code no matter the string length.

So, if you have a string that looks like this (the 4,000th position is in red bold font):

....SELECT * FROM sys.objects

SELECT * FROM sys.objects WHERE obj
ect_id LIKE '%' -- (this is the first line of the next 4,000-character chunk)

It prints out the following:

....SELECT * FROM sys.objects -- (this is the last line of the first chunk)

SELECT * FROM sys.objects WHERE object_id LIKE '%' --(this is the first line of the next chunk)
...

The last line of the first string is not printed out because we know we're going to run into problems if we do. So instead, we end that string prematurely and start the next string right after the premature end of the first one. This solution assumes that you are regularly inserting CR/LF characters in your Dynamic SQL, which I think is a must in order to keep them neatly formatted. Especially if they are very long strings.

Now, the bug...the only problem I haven't fixed yet is that an extra CR/LF is added to the printed out code. Notice the extra CR/LF in the last code sample above. However, this is a minor issue. The main thing is that the code can be copied/pasted into a new window and it will run without any manual massaging. When I have time to work on this further I'll fix the bug and repost the SP on my blog.

In summary, I think you will find this a handy SP for working with very long Dynamic SQL strings. It has served me well in printing out very large Dynamic SQL strings, which I find indispensable in debugging complex Dynamic SQL operations.

SB