T-SQL Conditional Order By

Question

I am trying to write a stored procedure that returns a list of object with the sort order and sort direction selected by the user and passed in as sql parameters.

Lets say I have a table of products with the following columns: product_id(int), name(varchar), value(int), created_date(datetime) and parameters @sortDir and @sortOrder

I want to do something like

select * from Product   if (@sortOrder = 'name' and @sortDir = 'asc')    then order by name asc   if (@sortOrder = 'created_date' and @sortDir = 'asc')    then order by created_date asc   if (@sortOrder = 'name' and @sortDir = 'desc')    then order by name desc   if (@sortOrder = 'created_date' and @sortDir = 'desc')    then order by created_date desc 

I tried do it with case statements but was having problems since the data types were different. Anyone got any suggestions?

Answer 1

CASE is an expression that returns a value. It is not for control-of-flow, like IF. And you can't use IF within a query.

Unfortunately, there are some limitations with CASE expressions that make it cumbersome to do what you want. For example, all of the branches in a CASE expression must return the same type, or be implicitly convertible to the same type. I wouldn't try that with strings and dates. You also can't use CASE to specify sort direction.

SELECT column_list_please FROM dbo.Product -- dbo prefix please ORDER BY    CASE WHEN @sortDir = 'asc' AND @sortOrder = 'name' THEN name END,   CASE WHEN @sortDir = 'asc' AND @sortOrder = 'created_date' THEN created_date END,   CASE WHEN @sortDir = 'desc' AND @sortOrder = 'name' THEN name END DESC,   CASE WHEN @sortDir = 'desc' AND @sortOrder = 'created_date' THEN created_date END DESC; 

An arguably easier solution (especially if this gets more complex) is to use dynamic SQL. To thwart SQL injection you can test the values:

IF @sortDir NOT IN ('asc', 'desc')   OR @sortOrder NOT IN ('name', 'created_date') BEGIN   RAISERROR('Invalid params', 11, 1);   RETURN; END  DECLARE @sql NVARCHAR(MAX) = N'SELECT column_list_please   FROM dbo.Product ORDER BY ' + @sortOrder + ' ' + @sortDir;  EXEC sp_executesql @sql; 

Another plus for dynamic SQL, in spite of all the fear-mongering that is spread about it: you can get the best plan for each sort variation, instead of one single plan that will optimize to whatever sort variation you happened to use first. It also performed best universally in a recent performance comparison I ran:

http://sqlperformance.com/conditional-order-by

Answer 2

You need a case statement, although I would use multiple case statements:

order by (case when @sortOrder = 'name' and @sortDir = 'asc' then name end)  asc,          (case when @sortOrder = 'name' and @sortDir = 'desc' then name end) desc,          (case when @sortOrder = 'created_date' and @sortDir = 'asc' then created_date end) asc,          (case when @sortOrder = 'created_date' and @sortDir = 'desc' then created_date end) desc 

Having four different clauses eliminates the problem of converting between types.