T-sql – CASE ORDER BY with multiple columns, and different sort options

caseorder-bystored-procedurest-sql

I am attempting to use a T-SQL CASE ORDER BY in a stored procedure where I am passed @OrderBy parameter as a TINYINT.

  • @Orderby = 1 Then Date column should be ASC
  • @Orderby = 2 Then Date column should be DESC

My question is: How can I get the date column to sort desc when I am passed a 2 for that parameter, and have the a string column sort asc in the same CASE ORDER BY statement?

This is what I have now for the CASE ORDER BY

    ORDER BY 
    CASE WHEN @OrderBy = 1 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) + tp.LastName + tp.FirstName END ,
    CASE WHEN @OrderBy = 2 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) + tp.LastName + tp.FirstName END DESC

This codes parses and returns a result set without error, but the 2nd CASE ORDER BY is all in DESC sort order, when I would prefer to have ccd.CertEndDate DESC , tp.LastName ASC , tp.FirstName ASC

Thanks in advance.

Best Answer

Break it out a little more:

ORDER BY CASE WHEN @orderby = 1 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) END ASC,
         CASE WHEN @orderby = 2 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) END DESC,
         tp.lastname ASC,
         tp.firstname ASC

You only need the sort order to change on the first field, so don't enclose the others in the CASE.

It should be noted that we don't include an ELSE for each CASE, which means any other value will return NULL and be discarded from the ORDER BY.

Related Question