CASE ORDER BY with multiple columns, and different sort options

Question

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.

• adopilot: here is my one maybe help dba.stackexchange.com/questions/7301/…

Answer 1

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.

• Eric Reid: Thank you, yes that was it. The developer passing me the parameter value is only passing a 1 or 2, but I may wrap some error handling around it just in case. Thanks again.

Answer 2

Along with JNK's answer, you could also consider:

DECLARE @Example TABLE
(
 first_name NVARCHAR(50) NOT NULL,
 last_name NVARCHAR(50) NOT NULL,
 cert_end_date DATE NOT NULL,
 other_columns NCHAR(100) NOT NULL DEFAULT (N'')
 UNIQUE (cert_end_date ASC, first_name, last_name),
 UNIQUE (cert_end_date DESC, first_name, last_name)
)
INSERT @Example
 (first_name, last_name, cert_end_date)
VALUES
 ('a', 'w', '2008-12-31'),
 ('b', 'x', '2009-12-31'),
 ('c', 'y', '2010-12-31'),
 ('d', 'z', '2011-12-31')
DECLARE
 @order_date_ascending BIT = CONVERT(BIT, 'true')
-- 1. May require an explicit sort despite useful indexes
SELECT
 e.first_name,
 e.last_name,
 e.cert_end_date
FROM @Example AS e
ORDER BY
 CASE WHEN @order_date_ascending = 1 THEN e.cert_end_date END ASC,
 CASE WHEN @order_date_ascending = 0 THEN e.cert_end_date END DESC,
 e.first_name ASC,
 e.last_name ASC
-- 2. Conditional statements
IF @order_date_ascending = CONVERT(BIT, 'true')
BEGIN
 SELECT
 e.first_name,
 e.last_name,
 e.cert_end_date
 FROM @Example AS e
 ORDER BY
 e.cert_end_date ASC,
 e.first_name ASC,
 e.last_name ASC
END
ELSE IF @order_date_ascending = CONVERT(BIT, 'false')
BEGIN
 SELECT
 e.first_name,
 e.last_name,
 e.cert_end_date
 FROM @Example AS e
 ORDER BY
 e.cert_end_date DESC,
 e.first_name ASC,
 e.last_name ASC
END
-- 3. Union All & Start-up Filters
SELECT * FROM 
(
 SELECT TOP (9223372036854775807)
 e.first_name,
 e.last_name,
 e.cert_end_date
 FROM @Example AS e
 WHERE
 @order_date_ascending = CONVERT(BIT, 'true')
 ORDER BY
 e.cert_end_date ASC,
 e.first_name ASC,
 e.last_name ASC
) AS sort_asc
UNION ALL
SELECT * FROM 
(
 SELECT TOP (9223372036854775807)
 e.first_name,
 e.last_name,
 e.cert_end_date
 FROM @Example AS e
 WHERE
 @order_date_ascending = CONVERT(BIT, 'false')
 ORDER BY
 e.cert_end_date DESC,
 e.first_name ASC,
 e.last_name ASC
) AS sort_desc

The first and third methods can benefit from the Parameter Embedding Optimization available on SQL Server 2008 SP1 CU5 if a statement-level compilation occurs, or is forced to occur using the OPTION (RECOMPILE) query hint. See this MSDN blog entry for details.

The third method does not require a compilation each time, since the Filter operators in the plan have a Start-Up Expression. The plan sub-tree below each Filter is only executed if the condition evaluates to true.