SQL Server Dynamic Order By Problem

Question

I'm trying to use a dynamic order by in a stored procedure so I can pass the order I want the data returned into the stored procedure as a parameter. This works fine for VARCHAR fields however if I try to sort an int or datetime field it errors the code I have is as follows

DECLARE @ORDERBY INT
SET @ORDERBY = 1
SELECT TOP 10 * FROM TBL_LMS_USERS_RECORDs_LAST_ATTEMPT
ORDER BY 
CASE 
    WHEN @OrderBy = 1 THEN s10_record_dow
    --WHEN @OrderBy = 2 THEN pk_big_record_id
    else s10_record_dow
END

If I uncomment the second WHEN in the case statement it errors with

"Error converting data type varchar to bigint."

I can order by this field fine if I dont use the case statement.

Any ideas?

Answer 1

Change it to this:

SELECT TOP 10 * FROM TBL_LMS_USERS_RECORDs_LAST_ATTEMPT
ORDER BY 
    CASE WHEN @OrderBy = 1 THEN s10_record_dow ELSE NUll END,  
    CASE WHEN @OrderBy = 2 THEN pk_big_record_id ELSE NULL END,
    CASE WHEN @OrderBy <> 1 AND  @OrderBy <> 2 THEN s10_record_dow ELSE NULL END

Answer 2

Why not use ROW_NUMBER()?

SELECT TOP 10 
       *,
       CASE @SortBy
         WHEN 'A' THEN ROW_NUMBER () OVER (ORDER BY s10_record_dow)
         WHEN 'B' THEN ROW_NUMBER () OVER (ORDER BY pk_id)
       END RowNumber
FROM TBL_LMS_USERS_RECORDs_LAST_ATTEMPT

Then the columns you order by can be of any type.