Return a subset of rows with a complex order by in T-SQL

Question

I have a stored proc select statement that lookes like that:

SELECT
    p.ID AS ID,
    p.Title AS Title,
    p.Text AS Text,
    p.CategoryID AS CategoryID,
    p.PostDate AS PostDate,
    p.Author AS Author,
    p.AuthorID AS AuthorID,
    p.IsApproved AS IsApproved,
    p.Rating AS Rating,
    p.RatesCount AS RatesCount,
    t.Text AS CategoryNameTranslation,
    p.IsEventPost AS IsEventPost,
    p.EventStart AS EventStart,
    p.EventEnd AS EventEnd,
    p.EventRegionID AS EventRegionID,
    p.EventAddress AS EventAddress
FROM
    Posts AS p
INNER JOIN
    Categories AS c
    ON c.ID = p.CategoryID
INNER JOIN
    Translations AS t
    ON c.TranslationID = t.ID
WHERE
    p.ID = CASE WHEN @ID != 0 THEN @ID ELSE p.ID END AND -- In case if ID given
    p.IsApproved = CASE WHEN @Approved != -1 THEN @Approved ELSE p.IsApproved END AND -- In case of approved status given
    t.Language = @LangID
ORDER BY
    CASE @OrderDirection
        WHEN 'Desc' THEN 
            CASE @OrderBy
                WHEN 'PostDate' THEN p.PostDate
            END
    END
    DESC,
    CASE @OrderDirection
        WHEN 'Asc' THEN 
            CASE @OrderBy
                WHEN 'PostDate' THEN p.PostDate 
            END
    END
    ASC;

I need to return a limited number of rows, but the ROW_NUMBER function requires an ORDER BY expression that is pretty complex here and it will be modified multiple times. Can the expression be somehow referenced from the main select statement or i just have to copy everything into the function's parameter?

Answer 1

SELECT TOP 10
....

Answer 2

I don't understand why you regard putting the ORDER BY into ROW_NUMBER as any more complex than putting it at the end of the query?

;WITH cte AS
(
SELECT
    p.ID AS ID,
    p.Title AS Title,
    p.Text AS Text,
    p.CategoryID AS CategoryID,
    p.PostDate AS PostDate,
    p.Author AS Author,
    p.AuthorID AS AuthorID,
    p.IsApproved AS IsApproved,
    p.Rating AS Rating,
    p.RatesCount AS RatesCount,
    t.Text AS CategoryNameTranslation,
    p.IsEventPost AS IsEventPost,
    p.EventStart AS EventStart,
    p.EventEnd AS EventEnd,
    p.EventRegionID AS EventRegionID,
    p.EventAddress AS EventAddress,
    ROW_NUMBER() OVER (
                ORDER BY
                    CASE @OrderDirection
                        WHEN 'Desc' THEN 
                            CASE @OrderBy
                                WHEN 'PostDate' THEN p.PostDate
                            END
                    END
                    DESC,
                    CASE @OrderDirection
                        WHEN 'Asc' THEN 
                            CASE @OrderBy
                                WHEN 'PostDate' THEN p.PostDate 
                            END
                    END
                    ASC
) AS RN
FROM
    Posts AS p
INNER JOIN
    Categories AS c
    ON c.ID = p.CategoryID
INNER JOIN
    Translations AS t
    ON c.TranslationID = t.ID
WHERE
    p.ID = CASE WHEN @ID != 0 THEN @ID ELSE p.ID END AND -- In case if ID given
    p.IsApproved = CASE WHEN @Approved != -1 THEN @Approved ELSE p.IsApproved END AND -- In case of approved status given
    t.Language = @LangID
) 
SELECT * 
FROM cte 
WHERE RN BETWEEN 11 AND 20
ORDER BY RN;