NVARCHAR(MAX) variable for building SQL Server 2008R2 dynamic query accepts only 4000 characters

Question

Can you assist me in understanding this odd NVARCHAR variable behavior?

I'm building a dSQL query that ranges in LEN() from about 6,700 to 6,900 characters, and stored it in a variable:

DECLARE 
   @SQL           NVARCHAR(MAX),
   @WhereClause   NVARCHAR(MAX) = 'WHERE 1 = 1 
',
   @OrderByClaose NVARCHAR(MAX)
SET @SQL = 
'SELECT
    <column list>
FROM
    <very complicated FROM clause>
 '

Unfortunately, I'm not allowed to post the actual code.

Initially, the dSQL was a long constant SELECT and FROM string of about 6,600 chars. The variable length dSQL WHERE and ORDER BY clauses were determined

IF <condition p>
   SET @WhereClause = @WhereClause + 'AND <condition p clause>
'

IF <condition q>
   SET @WhereClause = @WhereClause + 'AND <condition q clause>
'
etc.

and then tacked on to the end of @SQL.

The whole of @SQL would PRINT nicely and then execute with sp_executesql without a hitch.

So naturally the customer requires some changes, causing the nice constant string to be broken into several pieces. This required IF statements that add the dynamic column parts into the middle of the SELECT column list.

SET @SQL = '
SELECT <code through first few columns>
...
'  

IF <condition>
   SET @SQL = @SQL + 
'    <new dynamic column spec stuff A> 
'
ELSE
    SET @SQL = @SQL + 
'    <new dynamic column spec stuff B> 
'

SET @SQL = @SQL + ' <next block of constant chars>'
...

When I added the new IF/ELSE blocks, for some reason @SQL stopped accepting data after the first 4,000 characters, about halfway through the FROM clause.

Remove the IF/ELSE blocks and returning the SELECT and FROM to a constant string (just adding one of the new requirements cases for the test) and @SQL again happily accepted all 6,700 or so characters.

As a workaround, I changed the declaration of @SQL to VARCHAR(MAX)

DECLARE @SQL varchar(max)
SET @SQL = <The built-up query string with the new IF/ELSE blocks>

DECLARE @NSQL nvarchar(max) = cast(@SQL as nvarchar(max))
exec sp_executesql @NSQL

and it all ran fine.

Though I haven't tested it, I'm wondering if the only reason this workaround works for this particular task is because the character count in my largest possible dSQL statement is less than 8,000 chars.

I've taken a look at several articles, including

but nothing I've found seems to explain the behavior I've observed with this particular dSQL construction.

Any idea what's at work here?

Not really sure what the issue is. Since you need nvarchar to be passed to sp_executesql why not just use nvarchar from the beginning? — Sean Lange, Aug 20 '14 at 18:32
First of all, since you have a `NVARCHAR`, you should always use the `N'...'` format (with the leading `N` prefix) to clearly denote Unicode string literals. Secondly, if that alone doesn't help, try casting the string literals to `NVARCHAR(MAX)` before concatenation — marc_s, Aug 20 '14 at 18:33
Your second link (https://stackoverflow.com/questions/12639948/sql-nvarchar-and-varchar-limits) explains this exact behaviour, and how to overcome it. No?? — Dave C, Aug 20 '14 at 18:42
@Sean Lange I did initially declare SQL as NVARCHAR, as shown in the first bit of pseudocode. — Jim the Frayed, Aug 20 '14 at 18:43
@marc_s I didn't try your suggestions. I'll give them a shot. — Jim the Frayed, Aug 20 '14 at 18:44
@JiggsJedi Are you referring to "Truncation when concatenating depends on datatype."? I didn't see a case where adding some string text to a varchar or nvarchar variable was considered--only variable + variable. If you could point to the part that solves the problem, that would be quite helpful... — Jim the Frayed, Aug 20 '14 at 18:47
try using `ntext` data type instead, it allows you to use more than 1 billion characters. — King King, Aug 20 '14 at 18:50
I do this kind of stuff all the time without issues... but be aware that the PRINT command and looking at the results via SSMS will truncate even nvarchar(max) variables... which might lead you to falsely conclude the variable contents itself is being truncated. — pmbAustin, Aug 20 '14 at 18:50
@KingKing the ntext data type is deprecated... nvarchar(max) will allow more than a billion characters as well, and is the appropriate replacement for ntext. A future version of SQL Server will remove the ntext data type entirely. — pmbAustin, Aug 20 '14 at 18:51
@marc_s You called it! It doesn't PRINT, but the NVARCHAR(MAX) SQL now does contain all the code and runs correctly. Thanks! — Jim the Frayed, Aug 20 '14 at 18:56
@KingKing NO you don't want to use ntext. It has been deprecated since 2005 and is a serious PITA to work with. nvarchar(max) is much preferred over ntext. — Sean Lange, Aug 20 '14 at 19:00
@SeanLange I'm using SQL Server 2008 and it's ***still*** listed in the System Data Type list. — King King, Aug 20 '14 at 19:01
@pmbAustin Yep. Been there, done that. However, in this case, the fact that the PRINT was truncated clued me into the fact that there were likely other truncation issues afoot. Thanks-- — Jim the Frayed, Aug 20 '14 at 19:03
Deprecated does NOT mean removed. It means it still available but should no longer be used. http://msdn.microsoft.com/en-us/library/ms187993.aspx — Sean Lange, Aug 20 '14 at 19:06

score 2 · Accepted Answer · answered Aug 20 '14 at 18:59

First of all, since you have a NVARCHAR datatype, you should always use the N'...' format (with the leading N prefix) to clearly denote Unicode string literals.

DECLARE @WhereClause NVARCHAR(MAX) = N'WHERE 1 = 1'

Secondly, if that alone doesn't help, try casting the string literals to NVARCHAR(MAX) before concatenation

IF <condition>
   SET @SQL = @SQL + CAST(...... AS NVARCHAR(MAX))

NVARCHAR(MAX) variable for building SQL Server 2008R2 dynamic query accepts only 4000 characters

1 Answers1