Watch this week's video on YouTube
I write a lot of dynamic SQL and frequently encounter variables that contain many characters:
DECLARE @LongValue NVARCHAR(MAX) = CAST('' AS NVARCHAR(MAX)) +
N'SELECT
''A'' AS AShortValue,
'''+REPLICATE(N'A',4000)+''' as ALongValue
ORDER BY 1';
This variable is 4059 characters long, and when I execute it it runs great.
SELECT LEN(@LongValue); -- 4059 characters
EXEC(@LongValue);
A homage to one of my favorite Uncyclopedia entries.
If my programmatically built query had an error in it, the first thing I'd want to do when debugging it would be to see the the text of the entire @LongValue variable.
I could do this by just saying SELECT @LongValue, and while recent versions of SSMS will display the whole value for me, it completely loses my formatting which stinks (and is especially bad if there are any comments prefixed with -- in the query):
Need a ultra HD wide display to fit this all on one screen.
I can say PRINT @LongValue, which will keep the formatting, but it will get trimmed at 4,000 characters (notice the missing ORDER BY):
Some Better Ways
Erik Darling posts one solution to this problem in his T-SQL Tuesday #104 entry (as well as some other problems/solutions for lengthy SQL variables). Specifically he links to a SQL string printing script that will loop through the lengthy variable and print everything while maintaining formatting:
Not perfectly formatted, but good enough.
And while I like using that stored procedure on my primary server, I'm too lazy to install it every where I need it.
Instead, I have a couple of go-to solutions that work on all SQL Server instances 2008 forward.
Solution 1: CAST to XML
SELECT CAST(@LongValue AS XML) AS LongValue
Casting the long variable to XML allows SSMS to generate a clickable, single-row result that preserves formatting:
IntelliSense complains but I'm OK with it
The only downside to this approach is that certain charaters, like "<" and ">", can't be converted to XML:
Solution 2: FOR XML PATH
A slight variation on solution 1, we can get similar results using FOR XML PATH:
SET @LongValue = '<' + @LongValue -- Let's add in an invalid character
SELECT @LongValue FOR XML PATH('')
FOR XML PATH is one of the most abused SQL Server functions.
In this solution, the "<" is escaped to "<", which isn't perfect but at least my variable can be displayed with formatting intact. A quick find and replace for any escaped characters and I'm good to go.
Good Enough
These techniques aren't perfect, but for purposes of debugging dynamically generated code they are good enough.
Maybe one day SSMS will print longer strings or include a syntax formatter and I won't care nearly as much.
And if not, I'll happily continue to abuse FOR XML to do things other than generate XML documents.