Dynamic order direction

Question

I writing a SP that accepts as parameters column to sort and direction.

I don't want to use dynamic SQL.

The problem is with setting the direction parameter.

This is the partial code:

SET @OrderByColumn = 'AddedDate'
SET @OrderDirection = 1;

…

ORDER BY 
    CASE WHEN @OrderByColumn = 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
        WHEN @OrderByColumn = 'Visible' THEN CONVERT(varchar(2), Visible)
        WHEN @OrderByColumn = 'AddedBy' THEN AddedBy
        WHEN @OrderByColumn = 'Title' THEN Title    
    END

Answer 1

You could have two near-identical ORDER BY items, one ASC and one DESC, and extend your CASE statement to make one or other of them always equal a single value:

ORDER BY
      CASE WHEN @OrderDirection = 0 THEN 1
      ELSE
           CASE WHEN @OrderByColumn = 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
                WHEN @OrderByColumn = 'Visible' THEN CONVERT(varchar(2), Visible)
                WHEN @OrderByColumn = 'AddedBy' THEN AddedBy
                WHEN @OrderByColumn = 'Title' THEN Title
           END
      END ASC,
      CASE WHEN @OrderDirection = 1 THEN 1
      ELSE
           CASE WHEN @OrderByColumn = 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
                WHEN @OrderByColumn = 'Visible' THEN CONVERT(varchar(2), Visible)
                WHEN @OrderByColumn = 'AddedBy' THEN AddedBy           
                WHEN @OrderByColumn = 'Title' THEN Title
           END
      END DESC

Answer 2

You can simplify the CASE by using ROW_NUMBER which sorts your data and effectively converts it into a handy integer format. Especially since the question is tagged SQL Server 2005

This also expands easily enough to deal with secondary and tertiary sorts

I've used multiplier to again simplify the actual select statement and reduce the chance of RBAR evaluation in the ORDER BY

DECLARE @multiplier int;

SELECT @multiplier = CASE @Direction WHEN 1 THEN -1 ELSE 1 END;

SELECT
     Columns you actually want
FROM
    (
    SELECT
         Columns you actually want,
         ROW_NUMBER() OVER (ORDER BY AddedDate) AS AddedDateSort,
         ROW_NUMBER() OVER (ORDER BY Visible) AS VisibleSort,
         ROW_NUMBER() OVER (ORDER BY AddedBy) AS AddedBySort,
         ROW_NUMBER() OVER (ORDER BY Title) AS TitleSort
    FROM
         myTable
    WHERE
         MyFilters...
    ) foo
ORDER BY
     CASE @OrderByColumn
        WHEN 'AddedDate' THEN AddedDateSort
        WHEN 'Visible' THEN VisibleSort    
        WHEN 'AddedBy' THEN AddedBySort
        WHEN 'Title' THEN TitleSort
     END * @multiplier;

Answer 3

This works fine for me – (where, order by, direction, Pagination)

parameters

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


select * from items

order by 

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

OFFSET @start ROWS FETCH NEXT @limit ROWS ONLY