All You Can Print

In my previous post, Concatenating Strings in SQL Server, I mentioned that I wrote my own version of the PRINT statement. It’s a stored procedure called “PrintMax”. The reason I use this stored procedure is that the PRINT statement can’t handle a string with more than 8,000 bytes.

Here is a quote from the Microsoft Documentation:

“A message string can be up to 8,000 characters long if it is a non-Unicode string, and 4,000 characters long if it is a Unicode string. Longer strings are truncated. The varchar(max) and nvarchar(max) data types are truncated to data types that are no larger than varchar(8000) and nvarchar(4000).”

Sometimes, you want to print a long string. For example, you might want to print the definition of a long stored procedure. Or you might have a very long dynamic batch that you are going to execute, but you want to print it first for debug purposes.

The problem with the PRINT statement is not only that it prints up to the first 8,000 bytes. It also truncates your text without even generating a warning.

So I use my version of the PRINT statement – the “PrintMax” stored procedure – which does the following:

1. It accepts an NVARCHAR(MAX) input parameter. So any text value you pass (Unicode or non-Unicode, fixed-length or variable-length) is converted to NVARCHAR(MAX).

2. It splits the large text into chunks of up to 4,000 characters.

3. It searches for the last line break within each chunk and splits the string there. This way, it doesn’t just cut sentences in the middle. It uses existing line breaks in the text in order to perform the split. The output is much more readable.

You can download the “PrintMax” stored procedure from the Madeira Toolbox on GitHub.

Now, let’s try it…

First, let’s try to print a long string of X’s with both methods – the regular PRINT statement and the “PrintMax” stored procedure:

DECLARE @nvcLargeText AS NVARCHAR(MAX);

SET @nvcLargeText = REPLICATE (CAST (N'X' AS NVARCHAR(MAX)) , 5000);

SELECT LargeTextLength = LEN (@nvcLargeText);

PRINT @nvcLargeText;

EXECUTE dbo.PrintMax @inLargeText = @nvcLargeText;

As you can see, the PRINT statement truncates the text at 4,000 characters. The “PrintMax” stored procedure splits the text into two parts. It prints the first 4,000 characters in the first line and the additional 1,000 characters in the second line.

Now, let’s try to print the largest SQL module in your database:

DECLARE @nvcLargeText AS NVARCHAR(MAX);

SELECT TOP (1) @nvcLargeText = definition
FROM sys.sql_modules
ORDER BY LEN (definition) DESC;

SELECT LargeTextLength = LEN (@nvcLargeText);

PRINT @nvcLargeText;

EXECUTE dbo.PrintMax @inLargeText = @nvcLargeText;

See how the PRINT statement truncates the code in the middle without even warning you about it? And did you see how the “PrintMax” stored procedure prints the whole code nicely?