During an ordeal yesterday, I learned that you can't pass this query to EXEC():
@SQL = @SQL + 'WHERE ID = ' + @SomeID
EXCEC(@SQL)
Where @SomeID is an INT and @SQL is NVARCHAR. This will complain about not being able to convert NVARCHAR back to INT during execution.
I realized you have to do it like
@SQL = @SQL + 'WHERE ID = ' + CONVERT(NVARCHAR(20), @SomeID)
What I didn't understand is why? Why doesn't SQL Server understand the INT when simply +:ed on to an NVARCHAR? I'm guessing it has something to do with char sets.
EDIT: Fixed typos (missed some +:s).