Click here to Skip to main content
15,867,765 members
Articles / Database Development / SQL Server
Tip/Trick

SQL Server: CASE with Different Clause

Rate me:
Please Sign up or sign in to vote.
3.91/5 (8 votes)
5 Jul 2022CPOL2 min read 4.3K   11  
Using CASE with variable/SET, SELECT/UPDATE statement, WHERE clause, JOIN & ON clause
In this article, we will see how CASE can be used in any statement or clause that allows a valid expression.

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

SQL
CASE Expression
    WHEN Value1 THEN Result1
    WHEN Value2 THEN Result2
    …
    ELSE Result
END
SQL
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

SQL
CASE 
    WHEN BooleanExpression1 THEN Result1
    WHEN BooleanExpression2 THEN Result2
    …
    ELSE Result
END
SQL
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:

SQL
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

SQL
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

SQL
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

SQL
/*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

SQL
/*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:

SQL
/*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:

SQL
/*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:

SQL
/*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:

SQL
/*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:

SQL
/*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:

SQL
/*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:

SQL
/*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

SQL
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:

SQL
/*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:

SQL
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.

SQL
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

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

History

  • 5th July, 2022: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Bangladesh Bangladesh
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
-- There are no messages in this forum --