使用Print()可以打印8000多个字符

Question

如何打印包含8000个以上字符的字符串(例如动态查询(？

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

上面只打印@Qry的前8000个字符，并将其余字符剪掉。

Answer 1

问题是VARCHAR(MAX(最多可容纳2gb的数据，但print((只向终端打印8000个字符。因此，您必须将字符串分解为8000个字符块，然后分别打印它们。例如

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

Answer 2

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