SQL Case with 2 conditions

Question

I have a stored procedure that will accept 2 different parameters. The first parameter will determine which column I want to sort on, the second parameter will determine whether it is ASC or DESC

Create Procedure Some_SP
    @sortcolumn varchar(10)
    @sortorder varchar(10)
AS
    Select * from empTable
    Order by
         CASE @sortcolumn WHEN 'First_Name' THEN fname END,
         CASE @sortcolumn WHEN 'Last_Name' THEN lname END,
         CASE @sortcolumn WHEN 'ID' THEN empID END,
         CASE @sortorder WHEN 'ascending' THEN ASC END,
         CASE @sortorder WHEN 'descending' THEN DESC END

It is giving me syntax error. How do I fix it so that I can have 2 conditions in my CASE statement?

@TonyHopkinson I wasn't allowed to use dynamic SQL, it is part of our contract agreement with the client — C.J., Jul 16 '14 at 15:44
Fail to see why in this case, wrong arguments, SP is going to fall on it's arse anyway. — Tony Hopkinson, Jul 16 '14 at 15:46

Jon · Accepted Answer · 2014-12-12T20:06:38.680

5

The following will work:

Select * from empTable
Order by
CASE WHEN @sortcolumn = 'First_Name' AND @SortOrder = 'ascending' THEN fname END ASC,
CASE WHEN @sortcolumn = 'First_Name' AND @SortOrder = 'descending' THEN fname END DESC

etc...

In order to avoid typing each of these case statements by hand, you could write a "generator" script that you use to create this (especially good if the table definition would change):

SELECT 
    'CASE WHEN @SortColumn = ''' + C.name + ''' AND @SortOrder = ''ascending'' THEN ' + C.name + ' END ASC,' + CHAR(13) + CHAR(10) +
    'CASE WHEN @SortColumn = ''' + C.name + ''' AND @SortOrder = ''descending'' THEN ' + C.name + ' END DESC,'
FROM sys.columns C 
WHERE C.object_id = object_id('[Schema].[Table]')

edited Dec 12 '14 at 20:06

answered Jul 16 '14 at 15:43

Jon

122
12

Aye lot of etcetraing in there :( – Tony Hopkinson Jul 16 '14 at 15:44
Does that mean, if I have 5 different `column` choices, I would end up having 10 cases? – C.J. Jul 16 '14 at 15:45
@Tony Yeah, but as you said in your comment to the OP, Dynamic SQL would be a better route. If that's something that needs to be avoided, this is how I'd do it. – Jon Jul 16 '14 at 15:46
@C.J. Yes, it would give you 10 cases for 5 columns. – Jon Jul 16 '14 at 15:47
1

Yep without dynamic sql, no other option as far as I can see. When you need this sort of thing, an ORM is the best bet, course that would dynamically build the SQL, so it wouldn't be acceptable either. :) – Tony Hopkinson Jul 16 '14 at 15:49

score 2 · Answer 2 · answered Jul 16 '14 at 15:51

If you want to avoid dynamic SQL and using 2x your conditions, you can use row_number

eg:

declare @t table (string varchar(50), number int)

insert @t values ('a',9),('f',2),('c',1)

declare 
    @sc varchar(10) = 'number', -- or 'string', etc
    @so varchar(10) = 'desc' -- or 'asc'

select *
from 
(
    select 
        *,
        case @sc when 'string' then ROW_NUMBER() over (order by string)
                 when 'number' then ROW_NUMBER() over (order by number)
        end rn
    from @t
) v
order by 
case @so when 'desc' then -rn else rn end

This method, while interesting, has a huge performance impact if there are multiple columns involved. Ultimately, you're better off using sys.columns to generate my script if you really have to avoid using dynamic SQL. My method was about 24 times more efficient on a table with 50 columns than this method.. — Jon, Oct 15 '14 at 15:39

score 1 · Answer 3 · answered Jul 16 '14 at 15:52

You can just copy and paste and run this. I hate dynamic SQL, don't do it.

Unfortunately you'll have to duplicate the query....but it solves your specific problem.

DECLARE
    @sortcolumn varchar(10),
    @sortorder varchar(10)

SET @sortcolumn = 'fname'
SET @sortorder =  'DESC'

DECLARE
    @Data TABLE
        (
        fname nvarchar(10),
        lname nvarchar(10),
        empID int
        )
INSERT INTO @Data VALUES ('BBB', 'BBB', 2)
INSERT INTO @Data VALUES ('AAA', 'AAA', 1)


IF @sortorder = 'DESC' BEGIN

    SELECT
        *
    FROM 
        @Data
    ORDER BY
        CASE 
            WHEN @sortcolumn = 'fname' THEN fname
            WHEN @sortcolumn = 'lname' THEN lname
        END
        DESC

END ELSE BEGIN

    SELECT
        *
    FROM 
        @Data
    ORDER BY
        CASE 
            WHEN @sortcolumn = 'fname' THEN fname
            WHEN @sortcolumn = 'lname' THEN lname
        END

END

score 1 · Answer 4 · answered Jul 16 '14 at 18:06

1

Modifying Jon's answer to cap the ORDER BY list at just 2 instead of 2 * #columns

SELECT *
FROM MyTable
CROSS APPLY (VALUES
  ('First_Name',fname),
  ('Last_Name' ,lname),
  ('Id'        ,ID   )
) sort(SortColumn, SortValue)
WHERE SortColumn = @SortColumn
ORDER BY
  CASE @SortOrder WHEN 'ascending'  THEN SortValue END ASC,
  CASE @SortOrder WHEN 'descending' THEN SortValue END DESC

answered Jul 16 '14 at 18:06

Anon

10,660
1
29
31

Interesting idea, however the performance hit for the cross apply might not go unnoticed. I tried this on a table with 50 columns (all of which were sortable--you could improve this by having fewer columns to sort on), and just looking at the performance of the batch this method was 75% of the cost, mine was 25%. The cross apply adds a "constant scan" node to the query execution plan, an extra inner join, and an extra filter. The cost of my query is almost entirely in whatever table/index scan is happening and the sort. – Jon Oct 15 '14 at 14:52

SQL Case with 2 conditions

4 Answers4

Linked