T-SQL IF statements in ORDER BY clauses

Question

Is there a way I can do something like

@sortType

SELECT
    *
FROM
    table
ORDER BY
    -- if sortType == id
    table.Id
    -- else if sortType == date
    table.Date

How would that syntax look? Thanks.

Answer 1

There's a gotcha here... you can't mix data types in your case statement, so you need to create different groups of case statements for the different data types.

SELECT
    *
FROM
    table
ORDER BY
    CASE WHEN @SortType = id THEN table.Id END ASC,
    CASE WHEN @SortType != id THEN table.Date END ASC

Related blog post:

http://dirk.net/2006/11/14/dynamic-order-by-with-case-statement-in-sql-server-data-type-issues/

Answer 2

Try

ORDER BY
case when @sortType = 'id' then
table.Id
else 
table.Date end

Caution: That said, for performance reason it is probably better to split it up or use dynamic sql with sp_executesql

Answer 3

Don't ever do something like that. The resulted query plan will have to satisfy both conditions, since the plan is generic and not tied to a specific runtime value of the @variables. Such plan will miss a lot of possible optimizations. You should explicitly separate the queries:

@sortType

if @sortType == id
  SELECT
    *
  FROM
    table
  ORDER BY
    table.Id
else if @sortType == date
  SELECT
    *
  FROM
    table
  ORDER BY
    table.Date

This is the very same issue as trying to implement dynamic search conditions. The syntactic sugar of using a CASE in the ORDER BY is actually a query optimization blocker.

Answer 4

or just cast the date to an integer...

  ... ORDER BY case 
           When @sortType = 'id' Then table.Id 
           Else cast(table.Date As Integer) end