Dynamic Order by SELECT with multiple columns

Question

What is the correct MS SQL syntax to select multiple ORDER BY columns when the ORDER BY is based on a CASE statement?

The below works fine with single columns, but I need to sort by multiple columns:

SELECT * FROM Products ORDER BY  CASE WHEN @SortIndex = 1 THEN Price END ASC, CASE WHEN @SortIndex = 2 THEN Price DESC, Title ASC END <-- problem line 

Answer 1

You could try this

SELECT * FROM Products ORDER BY  CASE WHEN @SortIndex = 1 THEN Price END ASC, CASE WHEN @SortIndex = 2 THEN Price END DESC,  CASE WHEN @SortIndex = 2 THEN Title END ASC