order by using IF
-
I think you have to use Dynamic sql to do this, building the entire sql statement in a varchar variable and then using EXEC (@variable).
And then again, I might be wrong ...
David Webb -
You should be able to do this with a case statement...
eg:"ORDER BY CASE @sort WHEN 'Col1' THEN Column1 WHEN 'Col2' THEN Column2 ELSE Column3 END"....or some such!
**ASCII stupid question, get a stupid ANSI !!!** -
Let me first say that this is the kind of situation where it's better to sort at the client. But if it's not possible, then read on.
There's a catch with suchila's solution. The case statement will return the highest priority datatype. This means that you'll have to convert the numeric/dates columns to varchar and they will be sorted as varchar :
1
10
2
3...
To go around this you can use this technic :
Order by
Case when @sort = 'Col1' Then Col1 ELSE NULL end
, Case when @sort = 'Col2' Then Col2 ELSE NULL end
The downside of this technic is that the server will have to do a few useless sorts (order by null, null, col1, null, null). While I don't know the full cost of this it's surely not free... but that always works.
-
Suri - I forgot to add - "and what Remi says...." <;-)
**ASCII stupid question, get a stupid ANSI !!!** -
Your trick is good, but it still has a small pitfall. I preffer that he knows what he gets into before implementing to production.
No disrespect intended
. -
:cry
isrespect ?!?! I never took it that way Remi
.....was just pointing out to Suri that he needs to read through what you said...
**ASCII stupid question, get a stupid ANSI !!!** -
Oops, I taught for a sec that Suri was one of your nicknames. I may have missed something after all
. -
I got what I need. Thanks both of you Sushila and Remi.
-
HTH.
-
Remi we can remove the else NULL part. It works in the same way.
Regards,
gova -
Just a thought, but if you pass the column name as the sort parameter can you not just do
order by @sort
thus removing the need for any logic?
(similar to the way a DataSet works)
-
If only everything was so simple....
You cannot use @sort directly because variables are only allowed when ordering by an expression referencing a column name......
**ASCII stupid question, get a stupid ANSI !!!** -
Data Table or Data View can be sorted just by mentioning sort contion.
DV.Sort = "ColumnName ASC"; will work fine.
We don't have such thing in SQL. So sort it based on the passed name of column. Here the passed value should be made as a SQL statement. Without using CASE we cannot do it.
We can use Dynamic SQL. Then it will lead to permission and security issues.
Regards,
gova -
Yes, but I like to see it so I remember what happens if the case is false. But that's just me.
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply