How to print VARCHAR(MAX) using Print Statement?

More control with dynamic slicing

Now let's add some jazz to play this text game with more strategy:

DECLARE @Text VARCHAR(MAX) = 'Your sizzling soliloquy...'
DECLARE @ChunkSize INT = 8000
DECLARE @CurrentStart INT = 1
DECLARE @CurrentEnd INT
DECLARE @CurrentLength INT
DECLARE @KnockKnock CHAR(1) = CHAR(10) -- Who's there? Line break. Line break who? Line break your text down!

WHILE @CurrentStart < LEN(@Text)
BEGIN
    -- Finding where to slice without breaking the punchline!
    SET @CurrentEnd = CHARINDEX(@KnockKnock, @Text, @CurrentStart + @ChunkSize) - @CurrentStart
    SET @CurrentLength = CASE WHEN @CurrentEnd > 0 THEN @CurrentEnd ELSE @ChunkSize END

    PRINT SUBSTRING(@Text, @CurrentStart, @CurrentLength) -- Here's a slice of wisdom, hot from the oven!

    -- Ready, set, go for the next slice!
    SET @CurrentStart = @CurrentStart + @CurrentLength
END


See, we're being extra nice with our text by knocking on the doors (CHAR(10)) of every line break. We slice it nicely, and no funny business with weird line cuts.

Handling outlanders (special characters) and giants (large data)

Bring on the special characters and the VARCHAR(MAX) that make War and Peace look like a pamphlet. Here’s how we handle them:

DECLARE @Text NVARCHAR(MAX) = N'Your saucy script full of spice...'
-- Then roll with the punchlines just like we did before.


Got big data? No problems, we've got FOR XML PATH to save the day:

DECLARE @Script VARCHAR(MAX) = 'War and Peace in a shell...'
SELECT [processing-instruction(x)]=@Script FOR XML PATH(''), TYPE;


This is like nailing a Rubik's cube on the first twist, or seeing the entire Matrix in one glance. You are using the FOR XML and transcending the character limit of PRINT. Take that, Morpheus!

Armored for the real world

This dynamic strategy is forged in the crucibles of real-world use cases, it's built like a tank. From error log parsing to automatic report generation, from data sanity checks to large script executions, this method saves the day.

Automating PRINT statements, dynamically segmenting texts, and handling special characters, are not just trendy programmer biceps curls. They are industry standards to tackle text-heavy operations while using the tip of a SQL pen.