SQL Server 2008 : Row_Number() Always Ordering ASC

Question

I am trying to sort the rows on the basis of input parameters but it always sorting in ASC order.

CREATE PROCEDURE SP_NAME
    @order VARCHAR(4),
    @col VARCHAR(100),
    @locationId int,
AS
BEGIN
    SELECT 
        ROW_NUMBER() OVER (ORDER BY
                                   CASE WHEN @order = 'asc' AND @col = 'Time' 
                                           THEN [Time] 
                                   END ASC,
                                   CASE WHEN @order = 'desc' AND @col = 'Time' 
                                           THEN [Time] 
                                   END DESC) AS Row#,
        Col1, COl2, COl3,
    FROM 
        Table_ 
    WHERE 
        ID = @locationid
END

EXEC SP_NAME 'asc', 'Time' > Sorting in ASC
EXEC SP_NAME 'desc', 'Time' > Sortin in ASC

Please help me find the solution to this problem.

Answer 1

SELECT 
    Row# = CASE WHEN @order = 'DESC' THEN 
                ROW_NUMBER() OVER (ORDER BY
                               CASE WHEN @col = 'Time' THEN 
                                        [Time] 
                               END DESC) 
            ELSE
                ROW_NUMBER() OVER (ORDER BY
                               CASE WHEN @col = 'Time' THEN 
                                        [Time] 
                               END ASC) 
            END,
    Col1, COl2, COl3,
FROM 
    Table_ 
WHERE 
    ID = @locationid

Answer 2

There's no guaranty that the ROW_NUMBER() statement will sort your result set, you should do something like:

SELECT 
    ROW_NUMBER() OVER (ORDER BY
                               CASE WHEN @order = 'asc' AND @col = 'Time' 
                                       THEN [Time] 
                               END ASC,
                               CASE WHEN @order = 'desc' AND @col = 'Time' 
                                       THEN [Time] 
                               END DESC) AS Row#,
    Col1, COl2, COl3,
FROM 
    Table_ 
WHERE 
    ID = @locationid
ORDER BY Row# ASC