T-SQL dynamic Order By in Common Table for pagination

Question

I found some great sql code for dynamic sort & order but I am wondering if someone can help me re-jig it so that the CTE in the PROC below uses the dynamic sort/order. This code runs - but the output is not what I am after as the ORDER BY p.ProductId happens first in the CTE then the ORDER BY CASE statement only applies to the records 6 to 10

DECLARE @Skip int
DECLARE @Take int
DECLARE @OrderBy VARCHAR(50)

SET @Skip = 5;
SET @Take = 5;
SET @OrderBy = 'PriceAsc';


WITH ProductCT AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY p.ProductId) AS RowNum
    , p.ProductId
    FROM dbo.Product AS p
)
SELECT   p.ProductId
        ,p.Title
        ,p.Price
FROM dbo.Product AS p
INNER JOIN ProductCT AS pct ON pct.ProductId = p.ProductId
WHERE pct.RowNum BETWEEN @Skip + 1 AND (@Skip + @Take)
ORDER BY
    CASE     
         WHEN @OrderBy = 'TitleAsc' THEN (RANK() OVER (ORDER BY p.Title))
         WHEN @OrderBy = 'TitleDesc' THEN (RANK() OVER (ORDER BY p.Title DESC))
         WHEN @OrderBy = 'PriceAsc' THEN (RANK() OVER (ORDER BY p.Price))
         WHEN @OrderBy = 'PriceDesc' THEN (RANK() OVER (ORDER BY p.Price DESC))
         ELSE (RANK() OVER (ORDER BY p.Price))
    END

Thanks in advance for any suggestions :-)

Answer 1

I originally marked the answer from @Johan as correct because it worked, but I was a little unsure about the INNER JOIN also the overall complexity of this query.

I got chatting with a colleague of mine about the problem and he came up with this very tidy solution (thanks Tom!!) so I thought I would share it:

DECLARE @Skip int
DECLARE @Take int
DECLARE @OrderBy VARCHAR(50)

SET @Skip = 5;
SET @Take = 5;
SET @OrderBy = 'PriceAsc';


WITH ProductCT AS
(
    SELECT
    CASE
       WHEN @OrderBy = 'TitleAsc' THEN ROW_NUMBER() OVER (ORDER BY p.Title)
       WHEN @OrderBy = 'TitleDesc' THEN ROW_NUMBER() OVER (ORDER BY p.Title DESC)
       WHEN @OrderBy = 'PriceAsc' THEN ROW_NUMBER() OVER (ORDER BY p.Price)
       WHEN @OrderBy = 'PriceDesc' THEN ROW_NUMBER() OVER (ORDER BY p.Price DESC)
    END AS RowNum
    , p.ProductId
    FROM dbo.Product AS p
)
SELECT   p.ProductId
        ,p.Title
        ,p.Price
FROM dbo.Product AS p
INNER JOIN ProductCT AS pct ON pct.ProductId = p.ProductId
WHERE pct.RowNum BETWEEN @Skip + 1 AND (@Skip + @Take)
ORDER BY RowNum