CODEWITHSUNDEEP
t-sqldynamic-sql
anoopmishra217
anoopmishra217

Reputation: 61

Use Print() to print more than 8000 characters

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.

Upvotes: 4

Views: 7082

Answers (2)

Ruppert Russo
Ruppert Russo

Reputation: 1

declare 
   @msg nvarchar(max)
  ,@zeile nvarchar(500)
  ,@laenge bigint=0
  ,@aus bigint=0
  ,@nxt bigint=0

--select * from Monitor.PROMON01 with(nolock) where programm='Entwicklung'
select info from Monitor.PROMON01 with(nolock) where id='53236049-128E-44DE-8AA0-4B7F6247A3F8'
select @msg=info from Monitor.PROMON01 with(nolock) where id='53236049-128E-44DE-8AA0-4B7F6247A3F8'

set @laenge = len(@msg);
while @aus < @laenge
BEGIN
    set @nxt=CHARINDEX(char(13)+char(10),@msg,@nxt)
    set @zeile=substring(@msg,@aus,iif(@nxt-@aus>0,@nxt-@aus,0));
    print @zeile
    set @aus += LEN(@zeile)+2; 
    set @nxt+=2
END

Upvotes: -1

JeffUK
JeffUK

Reputation: 4251

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

Upvotes: 8

Related Questions