sql - nvarchar(max) still being truncated

Question

So I'm writing a stored procedure in MS SQL Server 2008. It's a really long query and I have to write it dynamically, so I create a variable called @Query and make it of type NVARCHAR(MAX). Now, I have been told that in modern versions of SQL Server, NVARCHAR(MAX) can hold a ridiculous amount of data, way more than the original 4000 character maximum. However, @Query is still getting truncated to 4000 characters when I try to print it out.

DECLARE @Query NVARCHAR(max);
SET @Query = 'SELECT...' -- some of the query gets set here
SET @Query = @Query + '...' -- more query gets added on, etc.

-- later on...
PRINT LEN(@Query) -- Prints out 4273, which is correct as far as I can tell
PRINT @Query      -- Truncates value to 4000 characters
EXEC sp_executesql @Query -- totally crashes due to malformed (truncated) query

Am I doing something incorrectly, or am I completely wrong about how NVARCHAR(MAX) works?

Answer 1

Problem seems to be associated with the SET statement. I think the expression can't be more than 4,000 bytes in size. There is no need to make any changes to any settings if all you are trying to do is to assign a dynamically generated statement that is more than 4,000 characters. What you need to do is to split your assignment. If your statement is 6,000 characters long, find a logical break point and then concatenate second half to the same variable. For example:

SET @Query = 'SELECT ....' [Up To 4,000 characters, then rest of statement as below]

SET @Query = @Query + [rest of statement]

Now run your query as normal i.e. EXEC ( @Query )