STAY IN TOUCH

Get New posts delivered straight to your inbox

  • Madeira Team

All You Can Print

In my previous post, Concatenating Strings, I promised to post my own version of the PRINT statement. It’s a stored procedure called “PrintMax”. The reason I use this stored procedure is because the PRINT statement can’t handle a string with more than 4,000 bytes.

Here is a quote from Books Online:

“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 4,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 a 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 here.

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:

Transact-SQL

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;

1

2

3

4

5

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 procedures 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:

Transact-SQL

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;

1

2

3

4

5

6

7

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?

#programming #tsql

JOIN OUR MAILING LIST

CONTACT US

 3 Rapaport St. Kfar Saba, Israel

 +972-9-7400101

  • Google+ - White Circle
  • Facebook - White Circle
  • Twitter - White Circle
  • LinkedIn - White Circle