SQL Server: CASE with Different Clause

• Download source code - 1.3 KB

Background

Here, we are going to check examples of how CASE can be used in any statement or clause that allows a valid expression. For example, we usually use CASE in statements such as SELECT, UPDATE, DELETE and SET. CASE can also be used with JOIN, WHERE, ORDER BY and HAVING clauses. Let us check some examples.

CASE Types

There are two types of CASE:

• Simple CASE: Allows only an equality check
• Searched CASE: Allows multiple checks

Simple CASE

CASE Expression
    WHEN Value1 THEN Result1
    WHEN Value2 THEN Result2
    …
    ELSE Result
END

DECLARE @userTypeId INT = 2;
DECLARE @userType VARCHAR(100);

/*Simple CASE*/   
SET @userType = CASE @userTypeId
                    WHEN 1 THEN 'Admin'
                    WHEN 2 THEN 'User'
                    ELSE 'Unknown'
                END;
SELECT @userType;

Searched CASE

CASE 
    WHEN BooleanExpression1 THEN Result1
    WHEN BooleanExpression2 THEN Result2
    …
    ELSE Result
END

DECLARE @userTypeId INT = 2;
DECLARE @userType VARCHAR(100);

/*Searched CASE*/   
SET @userType = CASE 
                    WHEN @userTypeId = 1 THEN 'Admin'
                    WHEN @userTypeId = 2 THEN 'User'
                    ELSE 'Unknown'
                END;
SELECT @userType; 

Alternatively, we can also do:

DECLARE @inputNumber INT = 2
DECLARE @result NVARCHAR(MAX);
SELECT 
    @result =    
    CASE 
        WHEN ISNUMERIC(@inputNumber) = 1 AND @inputNumber % 2 = 0  THEN 'Even'
        WHEN ISNUMERIC(@inputNumber) = 1 AND @inputNumber % 2 = 1  THEN 'Odd'
        ELSE 'Not Number'
    END;
SELECT @result AS Result;

With Variable

Use Like Switch

DECLARE @userTypeId INT = 2;    /*set any number*/
DECLARE @userType VARCHAR(100);

SET @userType = CASE @userTypeId
                    WHEN 1 THEN 'Admin'
                    WHEN 2 THEN 'User'
                    ELSE 'Unknown'
                END;
SELECT @userType;

Multiple Compare

DECLARE @inputNumber INT = 2
DECLARE @result NVARCHAR(MAX);
SELECT 
    @result =    
    CASE 
        WHEN ISNUMERIC(@inputNumber) = 1 AND @inputNumber % 2 = 0  THEN 'Even'
        WHEN ISNUMERIC(@inputNumber) = 1 AND @inputNumber % 2 = 1  THEN 'Odd'
        ELSE 'Not Number'
    END;
SELECT @result AS Result;

With SELECT Statement

Use Like Switch

/*use like switch*/
SELECT 
    Id, 
    CASE Id
        WHEN 1 THEN 'Admin'
        WHEN 2 THEN 'User'
        ELSE 'Anonymous'
    END
FROM (
    VALUES (1), (2), (NULL)
) AS Roles(Id) 

Multiple Compare

/*multiple compare*/
SELECT 
    NumberValue,
    CASE 
        WHEN ISNUMERIC(NumberValue) = 1 AND NumberValue % 2 = 0  THEN 'Even'
        WHEN ISNUMERIC(NumberValue) = 1 AND NumberValue % 2 = 1  THEN 'Odd'
        ELSE 'Not Number'
    END
FROM(
    VALUES ('1'), ('2'), ('A'), (NULL)
) AS NumberList(NumberValue)

With WHERE Clause

Here are our tables and data:

/*with where*/
DECLARE @tblUserPoints TABLE
(
    Name VARCHAR(100),
    UserType VARCHAR(100),
    Points INT
);
INSERT
INTO @tblUserPoints
VALUES 
('Dan', 'Sa', 90000),        /*will be selected*/
('Dan1', 'Sa', 80000),
('Ben', 'Admin', 70000),     /*will be selected*/
('Ben1', 'Admin', 60000),
('Kim', 'User', 50000),      /*will be selected*/
('Kim1', 'User', 40000); 

CASE Instead of OR

Here is an example of regular WHERE condition query:

/*regular 'OR' condition*/
SELECT *
FROM @tblUserPoints
WHERE (UserType = 'Sa' AND Points > 80000)
OR (UserType = 'Admin' AND Points > 60000)
OR (UserType = 'User' AND Points > 40000)

Rewriting above query using CASE:

/*using 'CASE' instated of 'OR'*/
SELECT *
FROM @tblUserPoints
WHERE (
    CASE 
        WHEN UserType = 'Sa' AND Points > 80000 THEN 1
        WHEN UserType = 'Admin' AND Points > 60000 THEN 1
        WHEN UserType = 'User' AND Points > 40000 THEN 1
        ELSE NULL
    END
) IS NOT NULL

The same way we can do things with AND conditions.

Switch Between Conditions Depending On Flag

Here, depending on @flag, the value we are actually switching between WHERE condition:

/*switch between conditions depending on a flag*/
DECLARE @flag VARCHAR(50);
--SET @flag = 'Best';
--SET @flag = 'Better';
SET @flag = 'Good';

SELECT *
FROM @tblUserPoints
WHERE (
    CASE 
        WHEN @flag = 'Best' AND Points >= 80000 THEN 1
        WHEN @flag = 'Better' AND Points >= 60000 AND Points < 80000 THEN 1
        WHEN @flag = 'Good' AND Points >= 40000 AND Points < 60000 THEN 1
        ELSE NULL
    END
) IS NOT NULL 

With JOIN and ON Clause

Our tables and data:

/*data*/
DECLARE @tblReputationTotal TABLE
(
    TotalPoint INT NULL,                
    Reputation VARCHAR(100)
);
DECLARE @tblReputation TABLE
(
    Point INT NULL,             /*sa*/
    RankPosition INT NULL,      /*admin*/
    Rating INT NULL,            /*user*/
    Reputation VARCHAR(100)
);
DECLARE @tblUserPoint TABLE
(
    Name VARCHAR(100),
    UserType VARCHAR(100),
    Point INT NULL,
    RankPosition INT NULL,
    Rating INT NULL
);
INSERT 
INTO @tblReputationTotal (TotalPoint, Reputation)
VALUES
(90000, 'Best'),
(70000, 'Better'),
(50000, 'Good');
INSERT 
INTO @tblReputation (Point, RankPosition, Rating, Reputation)
VALUES
(90000, NULL, NULL, 'Good Sa'),
(NULL, 70000, NULL, 'Good Admin'),
(NULL, NULL, 50000, 'Good User');
INSERT
INTO @tblUserPoint (Name, UserType, Point, RankPosition, Rating)
VALUES 
('Dan', 'Sa', 90000, NULL, NULL),       /*will be selected, Point*/
('Dan1', 'Sa', 80000, NULL, NULL),
('Ben', 'Admin', NULL, 70000, NULL),    /*will be selected, RankPosition*/
('Ben1', 'Admin', NULL, 60000, NULL),
('Kim', 'User', NULL, NULL, 50000),     /*will be selected, Rating*/
('Kim1', 'User', NULL, NULL, 40000); 

Here, depending on UserType column value, we are selecting join column:

/*query*/
SELECT u.*, r.Reputation
FROM @tblUserPoint AS u
JOIN @tblReputation AS r
ON CASE
        WHEN u.UserType = 'Sa' AND u.Point = r.Point THEN 1
        WHEN u.UserType = 'Admin' AND u.RankPosition = r.RankPosition THEN 1
        WHEN u.UserType = 'User' AND u.Rating = r.Rating THEN 1
        ELSE 0
    END = 1;

Alternatively, we can also do:

/*query*/
SELECT u.*, r.Reputation
FROM @tblReputationTotal AS r
JOIN @tblUserPoint AS u
ON r.TotalPoint =
    CASE
        WHEN u.UserType = 'Sa' THEN u.Point
        WHEN u.UserType = 'Admin' THEN u.RankPosition
        WHEN u.UserType = 'User' THEN u.Rating
        ELSE 0    /*can remove ELSE or set to -1. Don't set it to NULL*/
    END 

With Update

DECLARE @tblBalance TABLE (Amount INT NULL, NumberType VARCHAR(100) NULL);
INSERT INTO @tblBalance(Amount) VALUES (1), (2), (3), (4), (NULL);

/*update*/
UPDATE @tblBalance
SET NumberType =    CASE 
                        WHEN Amount % 2 = 1 THEN 'Odd'
                        WHEN Amount % 2 = 0 THEN 'Even'
                        ELSE 'Null Value'
                    END;

SELECT * FROM @tblBalance; 

With ORDER BY

Our tables and data:

/*data*/
DECLARE @tblEmployee TABLE
(
    Id INT,
    UserName VARCHAR(100),
    Email VARCHAR(100)
);
INSERT 
INTO @tblEmployee (Id, UserName, Email)
VALUES
(1, 'Dan', 'Dan@gmail.com'),
(2, 'Hen', 'Han@gmail.com'),
(3, 'Ben', 'Ben@gmail.com');


DECLARE @orderFilter VARCHAR(100);
SET @orderFilter = 'LoginName';
--SET @orderFilter = 'Email';

To manage order, we are using an order indicator flag @orderFilter.

@orderFilter will hold the expected column name or logic to be used for ordering a particular data set.

Different Columns But Same Order

Here, we are switching between columns UserName or Email or None('') but using the same order DESC:

SELECT *
FROM @tblEmployee
ORDER BY 
(
    CASE @orderFilter
        WHEN 'LoginName' THEN UserName
        WHEN 'Email' THEN Email
        ELSE ''                                /*need varchar, type as username 
                                                 and email are varchar*/
    END
) DESC,                                        /*can do individual column wise, 
                                                 check next query*/ 
Id ASC 

Column Wise Order

It is a bit different from the previous example. Here, order type can be specific to each column.

SELECT *
FROM @tblEmployee
ORDER BY 
CASE WHEN @orderFilter = 'LoginName' THEN UserName END DESC,
CASE WHEN @orderFilter = 'Email' THEN Email END ASC,
Id ASC 

UserName will be ordered as DESC or Email will be ordered as ASC depending on the column selection flag @orderFilter value.

Others

• With GROUP BY https://www.essentialsql.com/sql-case-statement/
• With HAVING

Limitations

• OR is not supported inside the boolean expression, instead of OR use IN()
• Avoid nested CASE, SQL Server allows for only 10 levels of nesting in CASE expressions. Instead use more WHEN

References

• Core: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017
• Join: https://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition
• Order: https://stackoverflow.com/questions/19486882/case-when-statement-for-order-by-clause

History

• 5^th July, 2022: Initial version