paginated query controlling classification opposite columns controlling row_number() over () sql server 2005

let's suspect i'm controlling northwind database i run query around stored way contains, among parameters, following:

• @offset infer where pagination starts,


• @limit infer page size,


• @sortcolumn infer buttress used classification purposes,


• @sortdirection, infer forebear heir sorting.

the thought pagination database, outcome set contains thousands rows caching an choice (and controlling viewstate even deliberate as, imo, sucks).

as competence know sql server 2005 provides duty returns consecutive array quarrel within assign outcome set, starting during 1 initial quarrel any partition.

we need classification each returned buttress (five example) enterprising sql an option, have twin possibilities: controlling excess if ... else ... carrying 10 queries, harm maintain, carrying query following:

with paginatedorders (
    select
        box (@sortcolumn + ':' + @sortdirection)
            'orderid:a' following row_number() over (order orders.orderid asc)
            'orderid:d' following row_number() over (order orders.orderid desc)
            'customerid:a' following row_number() over (order orders.customerid asc)
            'customerid:d' following row_number() over (order orders.customerid desc)
            'employeeid:a' following row_number() over (order orders.employeeid asc)
            'employeeid:d' following row_number() over (order orders.employeeid desc)
            'orderdate:a' following row_number() over (order orders.orderdate asc)
            'orderdate:d' following row_number() over (order orders.orderdate desc)
            'shippeddate:a' following row_number() over (order orders.orderid asc)
            'shippeddate:d' following row_number() over (order orders.orderid desc)
        finish rownumber,
        orderid, customerid, employeeid, orderdate, shippeddate
    orders
    -- where apportionment goes here
)
select
    rownumber, orderid, customerid, employeeid, orderdate, shippeddate,
    @offset, @limit, @sortcolumn, @sortdirection
from paginatedorders
where rownumber between @offset (@offset + @limit - 1)
order rownumber

i've attempted query several times, opposite arguments, the opening definitely good actually, nonetheless stills looks competence optimized way.

is anything wrong query way? introduce opposite approach?