Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
here is my one maybe help dba.stackexchange.com/questions/7301/… –  adopilot Dec 13 '11 at 20:47

2 Answers 2

up vote 5 down vote accepted

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.

share|improve this answer
    
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. –  Eric Reid Dec 13 '11 at 20:34

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.

share|improve this answer
1  
Just to let you know that method 1 is the only one currently working for the case @order_date_ascending BIT = CONVERT(BIT, 'false'). On method 2 both branches of the IF are the same. On method 3 both the TOP 100 PERCENT and the ORDER BY get optimised out. –  Martin Smith Dec 15 '11 at 12:25
1  
Thanks Martin! I can happily blame copy-n-paste for the method 2 thing, but I have no idea what I was thinking for method 3. Fixed now, thanks again :# –  Paul White Dec 15 '11 at 13:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.