Roman's Weekly SQL Server Tip - Getting around the 8K limit with EXEC()
First I'll start with a warning - avoid dynamic SQL as much as you can and always try to use sp_executeSQL instead of EXEC() if possible.
When I look at SQL code written by other developers, I often notice that many of them don't realize that you are not limited by the 8K limit when using a varchar to execute dynamic SQL with EXEC(); You can actually construct your dynamic SQL using multiple varchar variables and then execute them as an expression. The following code allows you to execute 16K of SQL code:
EXEC(@sql1 + @sql2)
You cannot do the same thing with sp_executeSQL directly, but you can nest the call to sp_executeSQL inside of EXEC() and convert the final string to Unicode:
EXEC('EXEC sp_executesql N''' + @sql1 + @sql2 + '''')