ROW_NUMBER() function in SQL 2005

Can this function accept parameter in the order clause?

WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description
FROM LOG)

Instead of using "ORDER BY Date DESC", I would like to use "ORDER BY @.SORTCOLUMN". But I could not get this to work properly.

Thanks,

There are several options:

1. Use dynamic SQL to generate the entire SELECT statement

2. Use CASE expression in the ORDER BY clause like:

ORDER BY case @.SortColumn when 1 then col1 end,

case @.SortColumn when 2 then col2 end

3. Use various SELECT statements with UNION operator to perform branching. This is best of both worlds.

There are advantages and disadvantages to these methods. By specifying column(s) directly in ORDER BY clause any covering index can be used whereas with CASE approach you lose that advantage. Dynamic SQL approach needs to be protected against SQL injection, requires additional permissions for caller, you can use execution context in SQL2005 and so on.

|||Thank you so much