CODEWITHSUNDEEP
sql-servert-sqlsorting
slandau
slandau

Reputation: 24102

T-SQL IF statements in ORDER BY clauses

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.

Upvotes: 15

Views: 34771

Answers (4)

SQLMenace
SQLMenace

Reputation: 135181

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

Upvotes: 6

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

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/

Upvotes: 29

Remus Rusanu
Remus Rusanu

Reputation: 294487

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.

Upvotes: 5

Charles Bretana
Charles Bretana

Reputation: 146603

or just cast the date to an integer...

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

Upvotes: 1

Related Questions