UNION ALL returns "conversion... overflowed an int column"

I had a stored procedure throwing the following error:

The conversion of the varchar value '9999999999' overflowed an int column.  

There's two queries joined, so I split them up to see which one was throwing the error.  

Neither.  Of course.  : - \

If you break the query apart, both halves work.  

Okay, so let's figure this out:

exec sp_describe_first_result_set @tsql = N'myqueryhere'

exec sp_describe_first_result_set @tsql = N'myotherqueryhere'

Oh, hey look, varchar in one, int in the other.

Cause? My own stupidness from 7 years ago:

case when fielda is null or fielda = 0 then 0

else replace(replace(convert(varchar(14),fielda(,'.',''))

end

On one set of data, there are fields that have values, specifically the 9999999999; the second set returns nothing.  

So it tries to convert to the "least" datatype, the INT, and the 9,999,999,999 is too large for INT, so it fails.

[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:
"myfieldbo.mytable"
Huh?

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