varchar(max) is truncating the string when set in a variable

Question

I'm trying to print a dynamic query but what's happening is while printing the query the characters are getting truncated.

declare @sql varchar(max)
set @sql = cast('select*from.................length is huge' as varchar(max))
print @sql

Any idea how to solve it?

Answer 1

The SQL function PRINT will only show a maximum of 8000 char, the variable still has the full content. Try running this:

PRINT LEN(@sql) 

You should see a much larger number.