CODEWITHSUNDEEP
sqlsql-server
Gavin
Gavin

Reputation: 17382

SQL Server Dynamic Order By

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?

Upvotes: 3

Views: 2426

Answers (3)

Buzzzzzzz
Buzzzzzzz

Reputation: 1204

order by 
(case when (@orderByAsc = 1) then r.SomeColumn end) asc,
(case when (@orderByAsc = 0) then r.SomeColumn end) desc

this is how you get ascening or descening

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300559

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

Upvotes: 9

AndyMcKenna
AndyMcKenna

Reputation: 2647

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.

Upvotes: 4

Related Questions