Breaking the Print character limit

I got some grief regarding my SQL Meme post about PRINT. I specifically stressed that I believe PRINT needs a make over because its inability to handle max data types, http://jahaines.blogspot.com/2010/05/sql-meme-tagged-5-things-sql-server.html.  I know I am not the only person out there that feels this functionality is a bit antiquated. In this post, I will provide a great alternative to PRINT.  I have been using this method for the past year or so to print really long dynamic SQL.  The concept is very simple.  Instead of printing the dynamic SQL to the messages tab, I will be converting the dynamic SQL to XML.  XML is a great alternative because it keeps the formatting and can hold up to 2 GB of data.  The key component here is naming the column [processing-instruction(x)].  This column name [processing-instruction(x)]sends special XML instruction allowing the text to be converted, along with any special characters.   It should be noted that whatever value you put in parenthesis will be incorporated in the XML tags, in my case “x”.

Let’s have a look at how this works.

DECLARE @sql VARCHAR(MAX)
SET @sql =
    CAST(REPLICATE('a',5000) + CHAR(13) AS VARCHAR(MAX)) +
    CAST(REPLICATE('b',5000) + CHAR(13) AS VARCHAR(MAX)) +
    CAST(REPLICATE('c',5000) + CHAR(13) AS VARCHAR(MAX)) + 
    'd'
    
SELECT [processing-instruction(x)]=@sql FOR XML PATH(''),TYPE

Pretty simple right!!! There really is not much to this technique.  It is very simplistic and gets the job done.  If you find yourself getting aggravated with makeshift PRINT solutions, come on over to the dark side and get your XML on.

Until next time, happy coding.