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 |
|
kajsa
Starting Member
2 Posts |
 Posted - 2002-08-07 : 10:05:30
|
| Hi!Is it possible to order a select-statement by a variable? I want to do something like this:SELECT *FROM Tab1ORDER BY @Order I get error code 1008 if I pass @Order as a column position or as a column name.What I can do is to parse the statement to a string and then do a exec on the string-statement. How will that affect performance?Thanks in advanceKajsa |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-08-07 : 10:12:15
|
| read me.Dynamic SQL may perform slower as most likely the execution plan will not be cached and/or reused. More importantly, Dynamic SQL means you have to grant explicit rights to the underlying objects to the user executing your proc.Jay White{0} |
 |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2002-08-07 : 10:15:34
|
| Hej Kajsa :)In this case I think it will be OK to use dynamic SQL, performance-wise. Just remember to think of the security implications (users must have select access on the table).Another way to do it is to have a hard-coded case statement in the order by clause. However that turns out to be slower than using dynamic SQL.Regards,Kalle Dahlberg |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-08-07 : 11:15:47
|
An example of using CASE in an ORDER BY clauseORDER BY CASE @column WHEN 1 THEN db1.notes WHEN 2 THEN CONVERT(char(8),db1.notedate,112) WHEN 3 THEN db1.enteredby WHEN 4 THEN CAST(db1.note_id AS varchar(20)) WHEN 5 THEN CAST(db1.inventory_id AS varchar(20)) WHEN 6 THEN db2.computername WHEN 7 THEN db2.deviceuser ELSE NULL END Edited by - jasper_smith on 08/07/2002 11:16:13 |
|
|
|
kajsa
Starting Member
2 Posts |
Posted - 2002-08-08 : 03:40:38
|
| CASE would have worked fine exept I have a UNION and the ordering is on cumputed columns./Kajsa |
|
|
|
|
|
|
Order a select-statement by a variable???