The best solution is to write ORDER BY clause with CASE statement. User can write one query and pass external variable to order the query with appropriate sort order. Let us see how we can write it.
The APPLY operator allows you to join two table expressions; the right table expression is processed every time for each row from the left table expression.
The CROSS APPLY operator returns only those rows from left table expression (in its final output) if it matches with right table expression. In other words, the right table expression returns rows for left table expression match only.
Cross apply and inner join returns same results
Outer Apply and Left outer join
Real life usage of APPLY operators
APPLY operator is required when you have to use table-valued function in the query, but it can also be used with an inline SELECT statements.
Following queries return same results. Row_Number() usage below;
PageSelect, paging in sql, row_number()
Partition By example;
CTE (Common table expression) example;
CTE and paging
An incorrectly composed recursive CTE may cause an infinite loop. For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. When testing the results of a recursive query, you can limit the number of recursion levels allowed for a specific statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement.