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

Here is an example:

CREATE PROCEDURE GetProducts 
( 
    @OrderBy      VARCHAR(50), 
    @Input2       VARCHAR(30) 
) 
AS 
BEGIN 
    SET NOCOUNT ON 

    SELECT Id, ProductName, Description, Price, Quantity 
    FROM Products 
    WHERE ProductName LIKE @Input2 
    ORDER BY 
        CASE             
            WHEN @OrderBy = 'ProductNameAsc' THEN ProductName 
        END ASC, 
        CASE 
            WHEN @OrderBy = 'ProductNameDesc' THEN ProductName 
        END DESC 

END

From here:

http://www.dominicpettifer.co.uk/Blog/21/dynamic-conditional-order-by-clause-in-sql-server-t-sql

Ascending and Descending actions need to be grouped into separate CASE statements, separated with a comma. In your server-side code/script make sure to append 'Asc' or 'Desc' onto the order by string, or you could have two Stored procedure input parameters for column name and order by direction if you want.

Answer 2

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 3

hope this link will guide you

Answer 4

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;