James James - 4 days ago 4
SQL Question

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?

Answer

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  
Comments