Tuesday, January 31, 2012

[Oddities] There's a limit to a SET assignment... Dynamic SQL, strings, and NVARCHAR

I had a query against a table that would change based on the day (mytable_20120131).  Copied and pasted it into a nvarchar(max) variable, tried to get it to run multiple times without luck.  Odd error messages - number of selects doesn't match number of inserts, "incorrect syntax near the keyword 'on'" (just means there's a problem somewhere in the code), problem near a comma, etc.  Was using nvarchar(max) since I'm plugging it into sp_executesql. Oh, and it's an explicit insert with over 140 fields.

Coworker had the bright idea to look at where the dynamic table name was added.  Did a "select @sql", copied and pasted into notepad.

declare @sql nvarchar(max), @mytable nvarchar(max)
set @mytable = 'dbo.mytable'
set @sql = 'insert into mytable ([myfielda],[myfieldb],..." + @mytable + ' on ...'
select @sql
Here's what it looked like around dbo.mytable when I did the select:

Turns out, there's a length to the string assignment: 4000 characters. I normally don't run into it because my code normally looks like this when I need dynamic sql, but I didn't want to have to deal with splitting the 30 lines I'd need:
set @sql = 'insert into  mytable ([myfielda],[myfieldb],.."
+ '[myfieldg],[myfieldh]...'
+ '[myfieldy],[myfieldz]...'

Fix?  In this case, an easy one:

set @sql = N'insert into mytable ([myfielda],[myfieldb],..." + @mytable + N' on ...'

It appears that because it's all nvarchar-assigned, that it never runs into the limit.

No comments: