CASE Statement (Transact-SQL) - [T-SQL]

We use case statement daily for getting smart and custom results out of data. It is such an easy(sometimes not the best) way to implement business logic.

But many of us don't realize that there are 2 types of case statements.

1. 
         A simple case statement

           CASE expression
                   WHEN value1 THEN result1
                   WHEN value2 THEN result2
                  ...
                  WHEN valueN THEN resultN                
                 ELSE else Result
          END

2.
         A searched case statement

        CASE
            WHEN Boolean_expression THEN result_expression [ ...n ] 
            [ ELSE else_result_expression ] 
        END

The main difference in syntax is that 

• The 1st one defines one expression BEFORE when clause 
  
• The 2nd one is much more flexible and you can define several expressions AFTER each when clause
  

Example:

--Simple Case


USE AdventureWorks


GO


SELECT 
CASE CategoryID
    WHEN 1 THEN 'Beverages'
    WHEN 2 THEN 'Condiments'
    WHEN 3 THEN 'Confections'
    WHEN 4 THEN 'Dairy Products'
    WHEN 5 THEN 'Grains/Cereals'
    WHEN 6 THEN 'Meat/Poultry'
    WHEN 7 THEN 'Produce'
    WHEN 8 THEN 'Seafood'
ELSE 'Unknown'
END
FROM [dbo].[Products]
                
--Search Case

SELECT 
CASE WHEN UnitsInStock < 25 THEN 'Understock'
          WHEN UnitsInStock BETWEEN 25 AND 35 THEN 'Good'
ELSE 'Over Stock'
END
FROM [dbo].[Products]