DESC and ASC as a parameter in stored procedure

Question

I have the following SP that I am using to paginate a list of news articles. As you may be able to guess, @count is the number of rows to return, @start is the index to select rows from (sorted by inner query), @orderby indicates the column to sort by, and @orderdir indicates whether to sort one direction or the other. My original query was here, before I added the @orderdir parameter.

ALTER PROCEDURE [mytable].[news_editor_paginate]
    @count int,
    @start int,
    @orderby int,
    @orderdir int
AS 
BEGIN
    SET NOCOUNT ON; 
    SELECT TOP (@count) * FROM 
    (  
        SELECT ne.*,n.publishstate, 
            (CASE WHEN @orderdir = 1 THEN
                ROW_NUMBER() OVER (
                    ORDER BY                    
                        CASE WHEN @orderby = 0 THEN ne.[time] END DESC,
                        CASE WHEN @orderby = 1 THEN ne.lastedit END DESC,    
                        CASE WHEN @orderby = 2 THEN ne.title END ASC
                    )
            WHEN @orderdir = 2 THEN
                ROW_NUMBER() OVER (
                    ORDER BY                    
                        CASE WHEN @orderby = 0 THEN ne.[time] END ASC,    
                        CASE WHEN @orderby = 1 THEN ne.lastedit END ASC,
                        CASE WHEN @orderby = 2 THEN ne.title END DESC
                    )
                END
            ) AS num
            FROM news_edits AS ne
            LEFT OUTER JOIN news AS n
            ON n.editid = ne.id 
        ) 
     AS a
    WHERE num > @start
END

Now nothing actually goes wrong, but the @orderby parameter doesn't work. If provide 1 as the @orderdir parameter, it will give me the exact same results as if I provide 2 as that parameter.

Solution 1

Row number isn't evaluated on every row, however case statements are so you're stuck with the rownum no matter what the case.

Try this instead:

            ROW_NUMBER() OVER (
                ORDER BY                    
                    CASE WHEN @orderby = 0 AND @orderdir = 1 THEN ne.[time] END DESC,    
                    CASE WHEN @orderby = 0 AND @orderdir = 2 THEN ne.[time] END ASC,    
                    CASE WHEN @orderby = 1 AND @orderdir = 1 THEN ne.lastedit END DESC,
                    CASE WHEN @orderby = 1 AND @orderdir = 2 THEN ne.lastedit END ASC,
                    CASE WHEN @orderby = 2 AND @orderdir = 1 THEN ne.title END ASC
                    CASE WHEN @orderby = 2 AND @orderdir = 2 THEN ne.title END DESC
                )

Solution 2

This works fine for me - (where,order by,direction,offset fetch)

       -- parameters

        @orderColumn  int ,
        @orderDir  varchar(20),
        @start  int ,
        @limit  int


        select * from items
        WHERE        (items.status = 1) 
        order by 

        CASE WHEN @orderColumn = 0 AND @orderdir = 'desc' THEN items.[category] END DESC,    
        CASE WHEN @orderColumn = 0 AND @orderdir = 'asc' THEN items.[category] END ASC,    
        CASE WHEN @orderColumn = 1 AND @orderdir = 'desc' THEN items.[category] END DESC,
        CASE WHEN @orderColumn = 1 AND @orderdir = 'asc' THEN items.[category] END ASC,
        CASE WHEN @orderColumn = 2 AND @orderdir = 'desc' THEN items.[category] END DESC,
        CASE WHEN @orderColumn = 2 AND @orderdir = 'asc' THEN items.[category] END ASC

        OFFSET @start ROWS FETCH NEXT @limit ROWS ONLY 

Comments

[James Hay]

Yup that worked exactly how I wanted, it seems so obvious now that you've written it down. Thanks, have to wait to mark this as the answer.

[Gats]

No sweat. Scratched my head over this stuff a few times before when row_number came out.