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
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
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
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