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.

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.

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.