I've been writing a lot of dynamic SQL that generates other SQL lately. While SQL isn't the best string manipulation language, it's got great native access to database schema information. However, my latest project was resulting in some very long scripts that coudn't be printed with PRINT, because it truncates the output after 8000 bytes, which is 8000 chars or 4000 nchars. One solution to this would have been to execute dynamic SQL to create a stored procedure containing the text, but in this case, I wanted to append multiple scripts together, so I chose to use PRINT as a simple way to output them all. The concept was simple: For each 4000 character Unicode block, find the last occurrance of CR, LF, or both, print the left part, and repeat on the right part. However, I ran into a few obstacles on the way; see my last two T-SQL Gotchas. Here is the final result, a general-purpose PRINT replacement that can handle any Unicode string, assuming that it contains at least one CR or LF every 4000 characters. The delimiter(s) could be parameterized, but I'll leave that as an exercise for you.

-- Works around the 4000/8000 character limit of the print statement
CREATE PROCEDURE dbo.LongPrint( @string nvarchar(max) )
AS
SET NOCOUNT ON

set @string = rtrim( @string )

declare @cr char(1), @lf char(1)
set @cr = char(13)
set @lf = char(10)

declare @len int, @cr_index int, @lf_index int, @crlf_index int, @has_cr_and_lf bit, @left nvarchar(4000), @reverse nvarchar(4000)
set @len = 4000

while ( len( @string ) > @len )
begin
   set @left = left( @string, @len )
   set @reverse = reverse( @left )
   set @cr_index = @len - charindex( @cr, @reverse ) + 1
   set @lf_index = @len - charindex( @lf, @reverse ) + 1
   set @crlf_index = case when @cr_index < @lf_index then @cr_index else @lf_index end
   set @has_cr_and_lf = case when @cr_index < @len and @lf_index < @len then 1 else 0 end
   print left( @string, @crlf_index - 1 )
   set @string = right( @string, len( @string ) - @crlf_index - @has_cr_and_lf )
end

print @string