CODEWITHSUNDEEP
sqlsql-servert-sql
Philip
Philip

Reputation: 2628

Order By Dynamically, multiple fields

I have the following statements in one of my SQL queries:

      CASE WHEN @Ordering = 'asc' THEN YEAR(TimeIn) ASC, DATEPART(WEEK, TimeIn) ASC END,
      CASE WHEN @Ordering = 'desc' then YEAR(TimeIn) DESC, DATEPART(WEEK, TimeIn) DESC END

but I'm getting an error:

Msg 156, Level 15, State 1, Procedure Line 43 [Batch Start Line 4] Incorrect syntax near the keyword 'ASC'.

I've tried searching for examples, but they all relate to one field, and not multiple as I'm trying to do there.

Can anyone advise how I can correct the above please?

Upvotes: 1

Views: 119

Answers (3)

Shushil Bohara
Shushil Bohara

Reputation: 5656

We can perform the same thing in little short way, please try it:

ORDER BY  
CASE WHEN @Ordering = 'asc'  THEN 
    CONCAT(YEAR(TimeIn),DATEPART(WEEK, TimeIn)) 
END ASC, 
CASE WHEN @Ordering = 'desc' THEN 
    CONCAT(YEAR(TimeIn),DATEPART(WEEK, TimeIn)) 
END DESC

Note: CONCAT can be used in SQL Server >= 2012, in lower version we can use + instead for concatenation.

Upvotes: 0

Z. Tian
Z. Tian

Reputation: 36

how about this

order by 
 (CASE @Ordering when 'asc' then 1 when 'desc' then -1 else 0 end)*TimeIn ASC,
 (CASE @Ordering when 'asc' then 1 when 'desc' then -1 else 0 end)*DATEPART(WEEK, TimeIn) ASC

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460098

You can repeat the same CASE, the direction comes after the END of the CASE:

ORDER BY  CASE WHEN @Ordering = 'asc'  THEN YEAR(TimeIn)           END ASC, 
          CASE WHEN @Ordering = 'asc'  THEN DATEPART(WEEK, TimeIn) END ASC,
          CASE WHEN @Ordering = 'desc' THEN YEAR(TimeIn)           END DESC, 
          CASE WHEN @Ordering = 'desc' THEN DATEPART(WEEK, TimeIn) END DESC

Upvotes: 4

Related Questions