November 21, 2006 at 10:26 pm
Is there a way to do a conditional order by so that a user can give a parameter to a stored proc and it give back results sorted the way they want?
I want it so that the user can do 1 of 4 things,
* sort by "title" ascending,
* sort by "title" descending,
* sort by "synopsis" ascending,
* sort by "synopsis" descending
Can it be done? This is what I have but I get a syntax error:
select * from Blah
Order By
Case
when @orderId = 1 then title asc
when @orderId = 2 then title desc
when @orderId = 3 then synopsis asc
when @orderId = 4 then synopsis desc
end
Any help is greatly appreciated!
November 22, 2006 at 3:40 am
Hi,
You can specify the column in this way but you cann't specify the Order.
There are two different way to accomodate your requirement.
One is the using dyanamic SQL which I should not recmend you and other is you can make the If clause on the top
Like
IF @orderId In (2,4)
select * from Blah
Order By
Case
when @orderId = 2 then title
when @orderId = 4 then synopsis
end desc
ELSE
select * from Blah
Order By
Case
when @orderId = 1 then title
when @orderId = 3 then synopsis
end ASC
I think this will slove your problem
Cheers
cheers
November 22, 2006 at 10:39 am
Something like this is also a possibility (and a mess):
order by
case when @sort1_direction = 1 then
case @sort1_columncode when 1 then title
when 2 then synopsis
else 'A'
end
else 'A'
end ASC,
case when @sort1_direction = 2 then
case @sort1_columncode when 1 then title
when 2 then synopsis
else 'B'
end
else 'B'
end DESC,
case when @sort2_direction = 1 then
case @sort2_columncode when 1 then title
when 2 then synopsis
else 'C'
end
else 'C'
end ASC,
case when @sort2_direction = 2 then
case @sort2_columncode when 1 then title
when 2 then synopsis
else 'D'
end
else 'D'
end DESCWhen including a numeric field you may need to convert it to a zero-padded varchar for sorting to avoid a datatype conflict. Like I said, a mess.
November 22, 2006 at 2:47 pm
Thanks for the replies guys, I was hoping it wouldnt be so difficult. I will try to find a way around the issue I think, rather than do something messy or duplicate code.
Thanks again
November 24, 2006 at 2:08 am
How about something like:
DECLARE @Order string
DECLARE @SQL string
SET @SQL = "SELECT * FROM Blah "
SET @Order = CASE WHEN
@orderId = 1 then "title asc"
when @orderId = 2 then "title desc"
when @orderId = 3 then "synopsis asc"
when @orderId = 4 then "synopsis desc"
@SQL = @SQL + @Order
EXECUTE(@SQL)
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
November 24, 2006 at 4:50 am
Take the easy way out, why don't ya?
(sorry. that was wrong of me. there's always a chance the original poster didn't understand that first reply.)
November 27, 2006 at 6:39 am
You can use something like what Dave I suggests, but use NULL to avoid data type conflicts.
Order by
Case when @Direction = 'Asc'
Then Case when @ColumnID = 1 then Col1 Else Col2 End -- which column to sort ascending
Else NULL end Asc, -- ascending term or NULL if direction is descending
Case when @Direction = 'Desc'
Then Case when @ColumnID = 1 then Col1 Else Col2 End -- which column to sort descending
Else NULL end Desc -- descending term or NULL if Direction is ascending
Not really a mess if you leave behind an explanation. It still might be faster to rewrite the whole thing as a set of select statements that conditionally execute based on the parameters, however. I seem to recall that it was not going to provide any benefit in my case, but the memory banks are old and unreliable.
jg
November 28, 2006 at 12:05 pm
Your syntax error (although not stated what it is exactly) is probably due to data type casting/conversion as described here:
http://www.extremeexperts.com/SQL/Articles/CASEinORDER.aspx
Will below work?
ORDER BY
CASE WHEN @orderId = 1 THEN title END ASC,
CASE WHEN @orderId = 2 THEN title END DESC,
CASE WHEN @orderId = 3 THEN synopsis END ASC,
CASE WHEN @orderId = 4 THEN synopsis END DESC
November 28, 2006 at 2:54 pm
It might do, Ill have a go in a little while and let you know.
November 29, 2006 at 8:49 am
Ah... I didn't think about breaking it out like that.
Still could get hard to manage as you add columns:
order by case when @sort1_columncode=1 and @sort1_direction=1 then Title else 'A' end ASC, case when @sort1_columncode=1 and @sort1_direction=2 then Title else 'A' end DESC, case when @sort1_columncode=2 and @sort1_direction=1 then Synopsis else 'A' end ASC, case when @sort1_columncode=2 and @sort1_direction=2 then Synopsis else 'A' end DESC, case when @sort2_columncode=1 and @sort2_direction=1 then Title else 'B' end ASC, case when @sort2_columncode=1 and @sort2_direction=2 then Title else 'B' end DESC, case when @sort2_columncode=2 and @sort2_direction=1 then Synopsis else 'B' end ASC, case when @sort2_columncode=2 and @sort2_direction=2 then Synopsis else 'B' end DESC
Thanks for the link
Don't try my sample (should work. revised it).
I just quickly tested it and am getting a 'A column has been specified more than once in the order by list. Columns in the order by list must be unique.' message.
That was why in my original post I did an ELSE 'A' and an ELSE 'B'.
For numeric columns you might have to do ELSE -1 and an ELSE -2
(positive integers have an unwanted effect).
If anyone has any insights on how else to avoid this error, please, let me know. Thanks
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply