T-SQL Dynamic Sorting and Order By

Using the CASE expression its possible to do efficient sorting and ordering by different parameters in one query. Here is an example from Greg Beech's Tech Blog which also includes paging:

DECLARE @SortType TINYINT, @SortAscending BIT, @FirstRow INT, @MaxRows INT;
SELECT @SortType = 2, @SortAscending = 0, @FirstRow = 10, @MaxRows = 10;

WITH FoundCustomers AS
(
    SELECT
        ROW_NUMBER() OVER
        (
            ORDER BY 
                CASE WHEN @SortType = 0 AND @SortAscending = 1 THEN c.ContactName END ASC
                ,CASE WHEN @SortType = 0 AND @SortAscending = 0 THEN c.ContactName END DESC
                ,CASE WHEN @SortType = 1 AND @SortAscending = 1 THEN c.CompanyName END ASC
                ,CASE WHEN @SortType = 1 AND @SortAscending = 0 THEN c.CompanyName END DESC
                ,CASE WHEN @SortType = 2 AND @SortAscending = 1 THEN c.Country END ASC
                ,CASE WHEN @SortType = 2 AND @SortAscending = 0 THEN c.Country END DESC
        ) AS RowNumber
        ,c.*
    FROM
        dbo.Customers c
)
SELECT
    fc.*
FROM
    FoundCustomers fc
WHERE
    fc.RowNumber BETWEEN @FirstRow AND @FirstRow + @MaxRows - 1
ORDER BY
    fc.RowNumber;