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