LISTING 2: Dynamic Ordering with Multiple CASE Statements

DECLARE @OrderByOption int
SET @OrderByOption = 2

SELECT  ProductId
 		,ProductName
FROM		products
ORDER BY
	CASE 	WHEN @OrderByOption = 1 THEN ProductId 		END
	,CASE	WHEN @OrderByOption = 2 THEN ProductName	END