Dynamic order direction

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

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 wantFROM    (    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...    ) fooORDER BY     CASE @OrderByColumn        WHEN 'AddedDate' THEN AddedDateSort        WHEN 'Visible' THEN VisibleSort            WHEN 'AddedBy' THEN AddedBySort        WHEN 'Title' THEN TitleSort     END * @multiplier;

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.