How to add a default clause in CASE

Question

How do I add a default clause in CASE statement? I have the following query:

DECLARE @SortColumn VARCHAR(50)

SELECT *
FROM @GroupList
ORDER BY 
    CASE WHEN @SortColumn = 'GroupName ASC' THEN GroupName END ASC,
    CASE WHEN @SortColumn = 'GroupName DESC' THEN GroupName END DESC,
    CASE WHEN @SortColumn = 'LCC ASC' THEN LiveConnectEmailClosure END ASC, 
    CASE WHEN @SortColumn = 'LCC DESC' THEN LiveConnectEmailClosure END DESC, 
    CASE WHEN @SortColumn = 'ES ASC' THEN EnableSharing END ASC,
    CASE WHEN @SortColumn = 'ES DESC' THEN EnableSharing END DESC,
    CASE WHEN @SortColumn = 'Description ASC' THEN GroupDescription END ASC, 
    CASE WHEN @SortColumn = 'Description DESC' THEN GroupDescription END DESC

    ELSE GroupName END ASC; -- this one doesn't work

gvee · Answer 1 · 2013-08-19T16:47:13.770

SELECT list
     , of
     , columns
     , GroupName
FROM   (
        SELECT list
             , of
             , columns
             , GroupName
             , CASE @SortColumn
                  WHEN 'GroupName ASC'    THEN GroupName
                  WHEN 'LCC ASC'          THEN LiveConnectEmailClosure
                  WHEN 'ES ASC'           THEN EnableSharing
                  WHEN 'Description ASC'  THEN GroupDescription
                END As ascending_order
              , CASE @SortColumn
                  WHEN 'GroupName DESC'   THEN GroupName
                  WHEN 'LCC DeSC'         THEN LiveConnectEmailClosure
                  WHEN 'ES DESC'          THEN EnableSharing
                  WHEN 'Description DESC' THEN GroupDescription
                END As descending_order
        FROM   @GroupList
       ) As x
ORDER
    BY CASE WHEN ascending_order IS NULL AND descending_order IS NULL THEN GroupName END ASC
     , ascending_order  ASC
     , descending_order DESC

Alternative option:

DECLARE @SortDirection char(4) = 'ASC';

IF @SortColumn LIKE '%DESC' THEN
  BEGIN
    SET @SortDirection = 'DESC';
  END;

SET @SortColumn = Replace(Replace(@SortColumn, ' DESC', ''), ' ASC', '');

SELECT list
     , of
     , columns
FROM   (
        SELECT list
             , of
             , columns
             , CASE @SortColumn
                  WHEN 'GroupName'   THEN GroupName
                  WHEN 'LCC'         THEN LiveConnectEmailClosure
                  WHEN 'ES'          THEN EnableSharing
                  WHEN 'Description' THEN GroupDescription
                  ELSE GroupName
                END As sort_column
        FROM   @GroupList
       ) As x
ORDER
    BY CASE WHEN @SortDirection =  'ASC' THEN sort_column END  ASC
     , CASE WHEN @SortDirection = 'DESC' THEN sort_column END DESC;

score 2 · Answer 2 · edited May 23 '17 at 12:06

Ok so heres the best i got for you You will still need to change 1 or 2 things

assuming:

create table GroupList (GroupName varchar(50),
                  LiveConnectEmailClosure varchar(50),
                  EnableSharing varchar(50),
                  GroupDescription varchar(50));

insert into GroupList values('GroupName1','YES','True','SomeDescrition');
insert into GroupList values('GroupName1','YES','True','SomeDescrition');
insert into GroupList values('GroupName1','NO','False','SomeDescrition');
insert into GroupList values('GroupName2','YES','True','SomeDescrition');
insert into GroupList values('GroupName2','YES','False','SomeDescrition');
insert into GroupList values('GroupName3','NO','True','SomeDescrition');
insert into GroupList values('GroupName3','NO','False','SomeDescrition');
insert into GroupList values('GroupName4','NO','True','SomeDescrition');
insert into GroupList values('GroupName4','NO','True','SomeDescrition');
insert into GroupList values('GroupName4','YES','False','SomeDescrition');

then

SELECT *
FROM GroupList
WHERE
CASE @SortColumn
WHEN 'GroupName ASC' THEN GroupName END ASC,
WHEN 'GroupName DESC' THEN GroupName END DESC,
WHEN 'LCC ASC' THEN LiveConnectEmailClosure END ASC, 
WHEN 'LCC DESC' THEN LiveConnectEmailClosure END DESC, 
WHEN 'ES ASC' THEN EnableSharing END ASC,
WHEN 'ES DESC' THEN EnableSharing END DESC,
WHEN 'Description ASC' THEN GroupDescription END ASC, 
WHEN 'Description DESC' THEN GroupDescription END DESC
ELSE GroupName END ASC;

thats the best i can do w/o a T-sql editor hope this helps

Beyond this: Please look at Florian Reischl's Answer

SELECT
   CASE 
   WHEN xyz.something = 1 THEN 'SOMETEXT'
   WHEN xyz.somethingelse = 1 THEN 'SOMEOTHERTEXT'
   WHEN xyz.somethingelseagain = 2 THEN 'SOMEOTHERTEXTGOESHERE'
   ELSE 'SOMETHING UNKNOWN'
   END AS ColumnName;

I like the second version, which closer to what I'm trying to accomplish. Yet, it's not working. Can u, please, write the whole select statement? I feel like I'm missing something. The idea is if nothing is supplied or what is supplied is different from what is in the list, the criteria should be "GroupName ASC" — Richard77, Aug 19 '13 at 16:28
The `ASC` needs to go at the **end** of the `CASE` statement. It cannot be mixed and matched. — gvee, Aug 19 '13 at 16:43
I have try several combinations. Can u please write a whole select statement? — Richard77, Aug 19 '13 at 18:17

How to add a default clause in CASE

2 Answers2