Parameter-driven ASC/DESC Sort ( CASE in ORDER BY )

Below query is an example for sorting the result set ascending or descending order.

DECLARE @SortDesc BIT = 1;

SELECT *

FROM Sales.SalesOrderHeader

ORDER BY

CASE WHEN @SortDesc = 1 THEN SalesOrderID END DESC,

CASE WHEN ISNULL(@SortDesc, 0) <> 1 THEN SalesOrderID END ASC ;

Above query will sort the result set in descending order. Change@SortDesc value to 0 to sort result set in ascending order.