SQL Server - Print MAX

Welcome to my blog, this is post numero uno.  I would like to thank Joe Stagner for hooking me up with a blog on weblogs.asp.net.  My name is Bennett Dill and I am an Application Architect at a market research firm in Cincinnati Ohio.  I have been programming for about ten years starting with ASP Classic and VB 6 right on up to C# and ASP.NET.  I don't know if I'm going to be a good blogger, but I'll give it a whirl.

Ok, so what about SQL Server & Print MAX?  From time-to-time, I deal with dynamic SQL.    Some of the dynamic queries get large, so NVARCHAR(MAX) was a welcome addition in MS SQL 2005.  Recently I encountered an error with a dynamic query, normally this isn’t a problem, print the SQL displaying the interpolated values and fix the typo.  Herein lies the problem, this SQL actually surpassed the NVARCHAR(4000), cool, the first SQL exceeding the old limit.  Why is that a problem?  Well, it seems as though the PRINT statement is still limited to NVARCHAR(4000)  or VARCHAR(8000).

I needed a fix for this and the fix needed to act just like the built-in PRINT function.  Breaking every 4000 characters and printing wasn’t an option because it would insert line breaks at unknown locations because each PRINT statement adds a line break.  While there may be an option to PRINT without a line break, I don’t know it, and will need to quickly update this post when someone points it out ;-).

Until then the fix is as follows, for a given string:

1. Walk it in 4000 character chunks
2. Find the last line break
3. Note it’s position
4. Print up to it
5. Skip it
6. Go back to step 1

Here is the SQL used to accomplish this (I apologize for not having a [code] style yet):

CREATE PROCEDURE [dbo].[PrintMax]
(
 @iInput NVARCHAR(MAX)
)
AS
BEGIN
 -- nothing we can do with null data
 IF (@iInput IS NULL) BEGIN
  RETURN;
 END;

 -- This procedure was created to properly print
 -- nvarchar(max) since the print statement can
 -- only handle NVARCHAR(4000), we break the
 -- input down into 4000 byte blocks and print
 -- upto the last linebreak before the 4000 byte cutoff
 DECLARE @ReversedData NVARCHAR(MAX)
  ,@LineBreakIndex INT
  ,@SearchLength INT;

 -- if the search length is less than the first occurance
 -- of a line break, the data will be printed with a line break
 -- at the SearchLength position even though there should not be
 -- a break in the data there.
 SET @SearchLength = 4000;

 -- only loop while the input is greater than the search length
 WHILE (LEN(@iInput) > @SearchLength) BEGIN
  -- obtain and reverse the input upto the search length
  SET @ReversedData = LEFT(@iInput, @SearchLength);
  SET @ReversedData = REVERSE(@ReversedData);

  -- determine the position of the first line break for this piece of data
  SET @LineBreakIndex = CHARINDEX(CHAR(10) + CHAR(13), @ReversedData);

  -- print the input only showing data upto the line break
  -- the original linebreak will not be displayed
  PRINT LEFT(@iInput, @SearchLength - @LineBreakIndex + 1);

  -- resize the input removing the data that was displayed and the line break.
  SET @iInput = RIGHT(@iInput, LEN(@iInput) - @SearchLength + @LineBreakIndex - 1);
 END;

 -- if there is any data remaining, print it
 IF (LEN(@iInput) > 0) BEGIN
  PRINT @iInput;
 END;
END;

All feedback is welcome, thanks!