Any way around the PRINT limit of nvarchar(max) in SSMS

This is a documented limitation of the print command in BOL. As a workaround, I use this stored procedure that I wrote:

CREATE PROCEDURE [dbo].[LongPrint]
      @String NVARCHAR(MAX)

AS

/*
Example:

exec LongPrint @string =
'This String
Exists to test
the system.'

*/

/* This procedure is designed to overcome the limitation
in the SQL print command that causes it to truncate strings
longer than 8000 characters (4000 for nvarchar).

It will print the text passed to it in substrings smaller than 4000
characters.  If there are carriage returns (CRs) or new lines (NLs in the text),
it will break up the substrings at the carriage returns and the
printed version will exactly reflect the string passed.

If there are insufficient line breaks in the text, it will
print it out in blocks of 4000 characters with an extra carriage
return at that point.

If it is passed a null value, it will do virtually nothing.

NOTE: This is substantially slower than a simple print, so should only be used
when actually needed.
 */

DECLARE
               @CurrentEnd BIGINT, /* track the length of the next substring */
               @offset tinyint /*tracks the amount of offset needed */


set @string = replace(  replace(@string, char(13) + char(10), char(10))   , char(13), char(10))

WHILE LEN(@String) > 1
BEGIN


    IF CHARINDEX(CHAR(10), @String) between 1 AND 4000
    BEGIN

           SET @CurrentEnd =  CHARINDEX(char(10), @String) -1
           set @offset = 2
    END
    ELSE
    BEGIN
           SET @CurrentEnd = 4000
            set @offset = 1
    END   


    PRINT SUBSTRING(@String, 1, @CurrentEnd) 

    set @string = SUBSTRING(@String, @CurrentEnd+@offset, 1073741822)   

END /*End While loop*/

This was originally posted on SQLServerCentral.com at http://www.sqlservercentral.com/scripts/Print/63240/

I must admit to being a bit confused with this, but sadly I dont have time to envestigate it any further at the moment.

I started playing with the code and altered it to

declare @y nvarchar(max)
declare @z nvarchar(max)
set @y = replicate('y' ,3999)
print len(@y) -- 3999
set @y = replicate('y' ,4000)
print len(@y) -- 4000
set @y = replicate('y' ,4001)
print len(@y) -- 4001
set @y = replicate('y' ,8001)
SELECT @z = 'z' + @y + @y
print len(@y) -- 8000
print len(@z) -- ?!?!?!
Select @z
go

And get the results
7999
8000
8000
3999
4000
4001
8000
16001

so... a variable can hold more than 8000 (16001 in this case). Using Copy/Paste to get the @z result into excel shows there are indeed 16001 characters in the variable

Maybe you can used the logic this is exhibiting to get around your problem?

Jonathan [Edit:]

Changing the line
set @y = replicate('y' ,4001)
to be
set @y = @y + @y
and that returns 8000 characters - I wonder if its a feature of REPLICATE?

[EDIT2] Gaaah, I;ve got caught up in it ..!

REPLICATE help in BoL ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/0cd467fb-3f22-471a-892c-0039d9f7fa1a.htm states that

If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.

I think REPLICATE AND/OR LEN have a limitation too:

declare @x varchar(max)
set @x = replicate('x' ,7999)
print len(@x) -- 7999
set @x = replicate('x' ,8000)
print len(@x) -- 8000
set @x = replicate('x' ,8001)
print len(@x) -- 8000

declare @y nvarchar(max)
set @y = replicate('y' ,3999)
print len(@y) -- 3999
set @y = replicate('y' ,4000)
print len(@y) -- 4000
set @y = replicate('y' ,4001)
print len(@y) -- 4001
set @y = replicate('y' ,8001)
print len(@y) -- 8000
go

Couldn't set anything bigger than 8000 on both varchar and nvarchar.

The same way you cant declare text variables, maybe you can't manipulate any string variable higher then 8000.

Even setting results to text, the select statement only returned the initial 8000 chars ( and the SSMS output to text limit is set to the max value of 8192 ).

These limitations are documented in BOL. I guess they must be legacy hangovers still present internally in PRINT from when 8000 was the 'max' for varchar and 4000 for nvarchar.

Some potential workarounds, but they might not fit your situation:

• add some logic to test the length of the variable and split into manageable chunks of 8000 or less varchars, and PRINT each one

• set 'Results to Text' and use select @x

Edit : As noted by some of the other answers, REPLICATE has it's own issues too, so be careful as to how you build up your string. It seems from you original question that your issue is still with PRINT and you just used REPLICATE in your question to demonstrate the point.