Conditional Order by?

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!

You need to split the query as ASC Query & Desc Query.

You can choose your order by columns (if it is single column) dynamically but Sorting Order you can't.

Use your query as follow as

if @.OrderId in (1,3)

select * from Blah
Order By
Case
when @.orderId = 1 then title
when @.orderId = 3 then synopsis
end asc

else

select * from Blah
Order By
Case
when @.orderId = 2 then title
when @.orderId = 4 then synopsis
end Desc

Or

You can use dynamic SQL

Declare @.SQL as NVarchar(1000)

Select @.SQL = N'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

Exec (@.SQL)

|||Why don't you dynamically add the final order by clause to the select query string ?
I guess it is more clear to understand and much more flexible for any future changes.|||Thanks both for the replies, I didnt know it would be so detailed, I have a rather large select query that I am applying this to and I dont really want to create a string then execute it, it just looks messy to me. But I guess if I have no option I guess ill have to.

Thanks again|||In some cases I have done this:

SELECT ....,
SortOrder = Case when @.orderId = 1 then title
when @.orderId = 2 then REVERSE(TITLE)
when @.orderId = 3 then synopsis
when @.orderId = 4 then REVERSE(synopsis) END
ORDER BY SortOrder

The only issue with this is all the vars in the CASE must be the same type, or cast to a certain type.

|||

Easiest is to do below:

order by

case @.orderId when 1 then title end

, case @.orderId when 2 then title end desc

, case @.orderId when 3 then synopsis end

, case @.orderId when 4 then synopsis end desc

Note that you will get the best performance (assuming you have indexes on the column(s) and the plan can use it) if you use dynamic SQL to form the ORDER BY with required columns or use different SELECT statements. But in most cases, I have found that the above construct is easier to use, safe from SQL injection (dynamic SQL is prone to it if you are not careful) and with few conversions which single CASE expression requires.

|||Thanks all for the help with this topic, I think Umachandar's answer will fit my solution best.