The CASE statement has the functionality of an IF-THEN-ELSE. It goes through conditions and returns a value when the first condition is met . So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
If there is no ELSE part and no conditions are true, it returns NULL.
There are two different types of format that can be used in CASE statement.
The simple CASE statement expression: It evaluates one expression against multiple values, once the condition and expression are matched, it returns the expression that is provided in THEN clause.
The searched CASE expression: It evaluates a condition using a comparison operators ,if condition is satisfied, It returns an expression corresponding THEN in the output.
SYNTAX
--Simple case expression: CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END --Searched CASE expression: CASE WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END
Input_expression
Is the expression that is evaluated when the simple CASE format is used.
WHEN when_expression
Is a simple expression to which input_expression is compared when the simple CASE format is used. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.
THEN result_expression
Is the expression that is returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE.
ELSE else_result_expression
Is the expression that is returned if no comparison operation evaluates to TRUE. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.
WHEN Boolean_expression
Is the Boolean expression that is evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.
Lets Look at an example of CASE Statement in SQL Server.
First we create a sample table named ITEM_DETAILS and insert some records in table.
CREATE TABLE ITEM_DETAILS (ID INT NOT NULL, ITEM VARCHAR(50), ITEM_QTY INT, ITEM_PURCHASED_DATE DATETIME) INSERT INTO ITEM_DETAILS (ID, ITEM, ITEM_QTY, ITEM_PURCHASED_DATE) VALUES (1, 'Scanner', 300, '2019-12-01 00:00:00.000'), (2, 'Scanner', 250, '2019-12-10 00:00:00.000'), (3, 'Scanner', 110, '2019-11-12 00:00:00.000'), (4, 'Scanner', 110, '2019-09-19 00:00:00.000'), (5, 'Scanner', 280, '2019-09-25 00:00:00.000'), (6, 'Scanner', 180, '2019-08-15 00:00:00.000'), (7, 'Scanner', 180, '2019-07-08 00:00:00.000'), (8, 'Pen Drive', 360, '2019-12-15 00:00:00.000'), (9, 'Pen Drive', 270, '2019-09-11 00:00:00.000'), (10, 'Pen Drive', 210, '2019-08-22 00:00:00.000'), (11, 'Pen Drive', 190, '2019-08-19 00:00:00.000'), (12, 'Pen Drive', 480, '2019-07-15 00:00:00.000'), (13, 'Pen Drive', 680, '2019-06-25 00:00:00.000'), (14, 'Pen Drive', 120, '2019-06-18 00:00:00.000'), (15, 'Hard Disk', 270, '2019-09-19 00:00:00.000'), (16, 'Hard Disk', 210, '2019-08-12 00:00:00.000'), (17, 'Hard Disk', 190, '2019-08-29 00:00:00.000'), (18, 'Hard Disk', 480, '2019-07-25 00:00:00.000'), (19, 'Hard Disk', 680, '2019-06-15 00:00:00.000'), (20, 'Hard Disk', 120, '2019-06-28 00:00:00.000')
A simple CASE statement expression
A simple case statement evaluates one expression against multiple values, once the condition and expression are matched, it returns the expression that is provided in THEN clause.
Suppose, you want to display a short code of an item in result set such as If item name is ‘SCANNER’ then code will be ‘SCN’, If ‘PEN DRIVE’ then ‘PD’, and ‘HD’ for ‘HARD DISK’.
For such requirement you can use simple case statement, following simple case expression evaluates Item value against multiple given values and return the short code that is given in THEN, If condition and expression are matched.
SELECT ID, ITEM, CASE ITEM Â WHEN 'SCANNER' THEN 'SCN' Â WHEN 'PEN DRIVE' THEN 'PD' Â WHEN 'HARD DISK' THEN 'HD' Â ELSE 'NOT MATCHED' END CODE, ITEM_PURCHASED_DATE FROM ITEM_DETAILS
As you can see Code column that contain the short code for an item.
The searched CASE expression:
It evaluates a condition using comparison operators, if condition is satisfied, It returns an expression corresponding THEN in the output.
Suppose, you want to set a quantity status based on the range of a quantity such as If quantity is between >=1 and <= 100 then status will be ‘Low Supply’, If quantity is between >100 and <=500 then status will be ‘Medium Supply’ , and status will be ‘High Supply’ for all quantity >500.
Following Search case expression returns the status based on the comparison of quantity and returns the status mentioned in THEN, if comparison expression is satisfied.
SELECT ID, ITEM, ITEM_QTY,CASE WHEN ITEM_QTY >=1 AND ITEM_QTY <=100 THEN 'LOW SUPPLY' WHEN ITEM_QTY >100 AND ITEM_QTY <=500 THEN 'MEDIUM SUPPLY' WHEN ITEM_QTY >500 THEN 'HIGH SUPPLY' ELSE 'NOT SATISIFED' END STATUS, ITEM_PURCHASED_DATE FROM ITEM_DETAILS
As you can see, it evaluates an expression and returns the status for each quantity.
Implementing Nested Case Statement
You can also use implement nested cast statement as per your requirement.
Following statement uses case inside case to get the desired output.
SELECT ID, ITEM, ITEM_QTY, CASE -- Outer Case WHEN ITEM ='Scanner' THEN CASE WHEN ITEM_QTY >= 200 THEN 'High Qty' -- Nested Case WHEN ITEM_QTY < 200 THEN 'Low Qty' END WHEN ITEM ='Pen Drive' THEN CASE WHEN ITEM_QTY >= 300 THEN 'High Qty' -- Nested Case WHEN ITEM_QTY < 300 THEN 'Low Qty' END WHEN ITEM ='Hard Disk' THEN CASE WHEN ITEM_QTY >= 400 THEN 'High Qty' -- Nested Case WHEN ITEM_QTY < 400 THEN 'Low Qty' END END AS STATUS FROM dbo.ITEM_DETAILS
Case statement can only be nested to Level 10 only
You can only nest case statement up to 10 level only, If you try to nest case statement more than 10 you will get following error.
Following case expression is nested to 11 level, and you can see it returns an error.
DECLARE @number INT SET @number = 11 SELECT Â CASE WHEN @number >= 1 THEN -- Level 1 Â Â CASE WHEN @number >= 2 then -- Level 2 Â Â Â CASE WHEN @number >= 3 then -- Level 3 Â Â Â Â CASE WHEN @number >= 4 then -- Level 4 Â Â Â Â Â CASE WHEN @number >= 5 then -- Level 5 Â Â Â Â Â Â CASE WHEN @number >= 6 then -- Level 6 Â Â Â Â Â Â Â CASE WHEN @number >= 7 then -- Level 7 Â Â Â Â Â Â Â Â CASE WHEN @number >= 8 then --Level 8 Â Â Â Â Â Â Â Â Â CASE WHEN @number >= 9 then--Level 9 Â Â Â Â Â Â Â Â Â Â Â CASE WHEN @number >= 10 then--Level 10 Â Â Â Â Â Â Â Â Â Â Â Â CASE WHEN @number >= 10 then--Level 11 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â @number End End End End End End End End End End End AS Number
Lets try to this case expression up to 10 level only.
DECLARE @number INT SET @number = 11 SELECT  CASE WHEN @number >= 1 THEN -- Level 1   CASE WHEN @number >= 2 then -- Level 2    CASE WHEN @number >= 3 then -- Level 3     CASE WHEN @number >= 4 then -- Level 4      CASE WHEN @number >= 5 then -- Level 5       CASE WHEN @number >= 6 then -- Level 6        CASE WHEN @number >= 7 then -- Level 7         CASE WHEN @number >= 8 then --Level 8          CASE WHEN @number >= 9 then--Level 9            CASE WHEN @number >= 10 then--Level 10                 @number End End End End End End End End End End AS Number
CASE Statement with ORDER BY Clause
Sometime, we have a requirement to sort the result set returned by select statement in a custom order , like to display any particular column value at top of result set, while remaining can be in standard sorting order.
Following statement displays the ITEM– ‘HARD DISK’ always at on the top of in sorting result set , regardless whatever the sorting order is used in select statement .
SELECT ID, ITEM, ITEM_QTY, ITEM_PURCHASED_DATE FROM ITEM_DETAILS ORDER BY CASE ITEM WHEN 'HARD DISK' THEN 1 ELSE 2 END
As you can see, all Item name that have value ‘HARD DISK’ is at on the TOP in sorting order, and remaining items follows the standard sorting order.
If you want read it in detail, read here – Custom sorting using case statement in Order By Clause
