Use Print() to print more than 8000 characters

Question

How can I print a string (e.g. a dynamic query) that contains more than 8000 chars?

Declare @sql varchar(max)
set @Qry='....(more than 8000 char)'
Print (@Qry)

The above only prints the first 8000 characters of @Qry and cuts off the rest.

Answer 1

The problem is that VARCHAR(MAX) holds up to 2gb of data, but print() only prints 8000, characters to the terminal. So you have to break up your string into 8000 char chunks and print them separately. e.g.

declare @test varchar(max);
declare @loop int = 1;
declare @length int =0;
declare @printed int =0;

/*build an exceptionally long string.*/
set @test= 'select ''1000000000000000000000000000000000000000000''';

while @loop < 1001 
begin
     set @test = @test + ',''12345678901234567890123456789012345678901234567890123456789012345678901234567890''';
     set @loop = @loop + 1;

end;
/*!build an exceptionally long string.*/

set @length = len(@test);
while @printed < @length
BEGIN
    print(substring(@test,@printed,8000));
    set @printed = @printed + 8000; 
END