Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-11-22 : 07:15:02
|
| Min writes "I'm working on a problem I'm sure is kind of common:1. Have a big candidate list, for each candidate there are a group of data collected;2. the system will keep define messages, each message is targetd to a group of Candiate who's data met target data.3. I'v decided to use the dynamical SQL to build the drivative table type of selection works fine. The only problem is that when the filter condition getting more complecated, the SQL statement will exceed the 4000 limitation. Any suggestions how to do the dynamical sql with more than 4000 characters?" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-22 : 07:32:49
|
| You can use exec instead which accepts a text dtatype - you can concatenate stringsexec (@sql1 + @sql2 + @sql3)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-11-22 : 12:13:54
|
| You can also check out the undocumented sp_execresultset procedure.rockmoose |
|
|
|
DaveM
Starting Member
2 Posts |
Posted - 2004-11-30 : 17:44:46
|
quote:
Originally posted by nr
You can use exec instead which accepts a text dtatype - you can concatenate stringsexec (@sql1 + @sql2 + @sql3)
Is this limited to 8000 characters?Dave |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-30 : 17:55:42
|
| If you use sp_executesql, then "The size of the string is limited only by available database server memory."For EXEC, Books Online only says "If the string is greater than 4,000 characters, concatenate multiple local variables to use for the EXECUTE string.".Tara |
|
|
|
|
|
|
need use sp_executesql to run long SQL statement (more than 4000 nvarchar)