Thursday, July 8, 2010

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

image

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.

10 comments:

Jim Carnicelli said...

Great sample. I wasn’t getting it at first until I realized you have to click on the XML in the Results grid. Slick trick!

weissler said...

M a r v e l o u s! That is just awesome.

yrtimiD said...
This comment has been removed by the author.
yrtimiD said...

Actually, doing:
SELECT CONVERT(XML, @sql)
results in a full string outputed without any xml tags.

Adam Haines said...

yrtimiD,

True, you can do a simple cast, but you will encounter problems with special characters such as ">" and "<" etc becuase of the encoding. That is why I use processing instruction.

Joshua Smith said...

Thanks for your help in describing this. Nice review! Also turn your attention that it needs to search for mobile software development companies if you need development mobile.

Anonymous said...

This is just AWESOME!!

Clay said...

I would still like the SQL team to fix PRINT, but meanwhile this is fantastic. Exactly what I needed.

Nilesh said...

That's great tip! Thanks for sharing!

Steve said...

Thanks - that's some nice sleuth-work.