DESC and ASC as a parameter in stored procedure

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                )

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