Roman Rehak

SQL Server and things not related

<January 2009>
SuMoTuWeThFrSa
28293031123
45678910
11121314151617
18192021222324
25262728293031
1234567


Navigation

SQL Server Sites

Subscriptions

News

eXTReMe Tracker

Post Categories



Sunday, May 15, 2005 - Posts

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 + '''')

posted Sunday, May 15, 2005 10:19 PM by Roman with 0 Comments




Powered by Dot Net Junkies, by Telligent Systems