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 |
|
codeasp
Starting Member
1 Post |
 Posted - 2005-02-27 : 19:08:19
|
| Hi everybodyI have a query, which takes a lot of params (in the where clause) including a order by param. The result set I get is inserted in the table variable where depending upon the pageno, only certain amount of records are pulled.I cannot use the case statement in order by because the datatypes are not the same. I do not want to use dynamic sql (exec(@stmt)). Can use the sp_executesql at the moment, but am worried, if some more columns are added, then this wouldn't work (will statement will be more than 4000 characters).Any suggestions!!! Your help is greatly appreciated. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-02-27 : 22:02:43
|
| If you're inserting the results into a table variable then you won't be able to use dynamic sql at all (including sp_executesql). However, if you don't use a table variable and you do use dynamic sql, you wouldn't need to worry about a 4000 character limit because varchar variables can hold 8000 characters. Even if you had a larger statement then 8000 you could use multiple variables ( ie: exec(@sql1 + @sql) ). One advantage of the dynamic sql route is you usually can greatly simplify the statment.Be One with the OptimizerTG |
 |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
|
|
|
|
|
Order By - variable