I'm writing a stored procedure in 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?
To see the dynamic SQL generated, change to text mode (shortcut: Ctrl-T), then use SELECT
PRINT LEN(@Query) -- Prints out 4273, which is correct as far as I can tell
--SET NOCOUNT ON
SELECT @Query
As for sp_executesql, try this (in text mode), it should show the three aaaaa...'s the middle one being the longest with 'SELECT ..' added. Watch the Ln... Col.. indicator in the status bar at bottom right showing 4510 at the end of the 2nd output.
declare @n nvarchar(max)
set @n = REPLICATE(convert(nvarchar(max), 'a'), 4500)
SET @N = 'SELECT ''' + @n + ''''
print @n -- up to 4000
select @n -- up to max
exec sp_Executesql @n