Nov 192015
 

Quite often, I output information through PRINT command. It works well only when message is shorter than 8000 bytes. When the message is greater than 8000 bytes, extra-characters will be removed. the simplest way to overcome this is to make a loop and print 8000 characters at a time

declare @line nvarchar(max) = Replicate(cast('0123456789' as varchar(max)), 800)+'abc
'
select @line = replicate(@line, 3)
declare @pos int = 1
while @pos <= len(@line)
begin
	print substring(@line, @pos, 8000)
	select @pos = @pos + 8000
end
/*
012345678901234567890....
abc
012345678901234567890....
6789abc
012345678901234567890....
23456789abc
*/


It seems good until I look at the line 12, 14, 16 above. what I really want to see is the first line contains numbers and the second line are letters, then the third line are numbers and fourth line are letters. Here is the procedure to display them correctly

create procedure PrintString (@str varchar(max))
as
begin
	declare @line varchar(max), @StartLocation int, @Length int, @TotalLength int, @Current int
	select @StartLocation = 1, @TotalLength = datalength(@str), @Current = 1, @Length = 0
	declare @PrintLine nvarchar(max) =
'declare @pos int = 1
while @pos <= len(@line)
begin
	print substring(@line, @pos, 8000)
	select @pos = @pos + 8000
end'
	while @Current <= @TotalLength
	begin
		if(substring(@str, @Current, 2) in( char(0x0d) + char(0x0a), char(0x0a) + char(0x0d)))
		begin
			if @Length <= 0
				print ''
			else
			begin -- line
				select @line = substring(@str, @StartLocation, @Length)
				exec sp_executesql @PrintLine, N'@Line varchar(max)' , @line
			end
			select @StartLocation = @Current + 2, @Current = @Current + 2, @Length = 0
			continue;
		end
		else if (substring(@str, @Current, 1) in(char(0x0d) , char(0x0a)))
		begin
			if @Length <= 0
				print ''
			else
			begin
				select @line = substring(@str, @StartLocation, @Length)
				exec sp_executesql @PrintLine, N'@Line varchar(max)' , @line
			end
			select @StartLocation = @Current + 1, @Current = @Current + 1, @Length = 0
			continue;
		end
		select @Current = @Current + 1, @Length = @Length + 1
	end
	if(@StartLocation <= datalength(@str))
		print substring(@str, @StartLocation, datalength(@str))
end
go
declare @str nvarchar(max) = Replicate(cast('0123456789' as varchar(max)), 800)+'abc
'
select @str = replicate(@str, 3)
exec PrintString @str
/*
01234567890...
abc
01234567890...
abc
01234567890...
abc

*/

Everything looks good now.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="">

(required)

(required)

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |