Passing Order By as parameter

A couple months ago, Itzik Ben-Gan offered a nice, paramaterized Order By
solution:
[url]http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/t
hread/ae2994003a03954f/1ad2f13eb630fa4e?q=Daniel+Wilson&rnum=1#1ad2f13eb630fa4e[/u
rl]
select * from titles
order by
case when @.col = 'title' and @.dir = 'asc' then title end,
case when @.col = 'title' and @.dir = 'desc' then title end desc,
case when @.col = 'price' and @.dir = 'asc' then price end,
case when @.col = 'price' and @.dir = 'desc' then price end desc,
..
Now I have a situation with multiple fields on which to sort, one of them
being descending. How can I use this method to come out with ORDER BY Price
DESC, Title ?
Thanks.
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
DVBrown Company
(864)292-5888You could try ORDER BY -price
"Daniel Wilson" <d.wilson@.Embtrak.com> wrote in message
news:uEOm$UzzFHA.908@.tk2msftngp13.phx.gbl...
>A couple months ago, Itzik Ben-Gan offered a nice, paramaterized Order By
> solution:
> [url]http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm
/thread/ae2994003a03954f/1ad2f13eb630fa4e?q=Daniel+Wilson&rnum=1#1ad2f13eb630fa4e[
/url]
> select * from titles
> order by
> case when @.col = 'title' and @.dir = 'asc' then title end,
> case when @.col = 'title' and @.dir = 'desc' then title end desc,
> case when @.col = 'price' and @.dir = 'asc' then price end,
> case when @.col = 'price' and @.dir = 'desc' then price end desc,
> ...
> Now I have a situation with multiple fields on which to sort, one of them
> being descending. How can I use this method to come out with ORDER BY
> Price
> DESC, Title ?
> Thanks.
> Daniel Wilson
> Senior Software Solutions Developer
> Embtrak Development Team
> DVBrown Company
> (864)292-5888
>|||On Wed, 12 Oct 2005 09:59:33 -0400, Daniel Wilson wrote:

>A couple months ago, Itzik Ben-Gan offered a nice, paramaterized Order By
>solution:
>[url]http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/
thread/ae2994003a03954f/1ad2f13eb630fa4e?q=Daniel+Wilson&rnum=1#1ad2f13eb630fa4e[/
url]
>select * from titles
>order by
> case when @.col = 'title' and @.dir = 'asc' then title end,
> case when @.col = 'title' and @.dir = 'desc' then title end desc,
> case when @.col = 'price' and @.dir = 'asc' then price end,
> case when @.col = 'price' and @.dir = 'desc' then price end desc,
> ...
>Now I have a situation with multiple fields on which to sort, one of them
>being descending. How can I use this method to come out with ORDER BY Pric
e
>DESC, Title ?
>Thanks.
Hi Daniel,
select * from titles
order by
case when @.col1 = 'title' and @.dir1 = 'asc' then title end,
case when @.col1 = 'title' and @.dir1 = 'desc' then title end desc,
case when @.col1 = 'price' and @.dir1 = 'asc' then price end,
case when @.col1 = 'price' and @.dir1 = 'desc' then price end desc,
case when @.col2 = 'title' and @.dir2 = 'asc' then title end,
case when @.col2 = 'title' and @.dir2 = 'desc' then title end desc,
case when @.col2 = 'price' and @.dir2 = 'asc' then price end,
case when @.col2 = 'price' and @.dir2 = 'desc' then price end desc,
..
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)