UTIS
  • Home
  • TECH TRAINING
  • IT CONSULTING
  • IT RECRUITING
  • OUR CLIENTS
  • Contact
  • Blog

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

3/12/2015

0 Comments

 
Picture
Picture
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]
0 Comments

    Arun Albert

    He believe in the power of raw data. Unaltered data has the solutions for the problems and insight to a better world. 

    Archives

    March 2015

    Categories

    All
    SQL Server
    T-SQL

    RSS Feed

Powered by Create your own unique website with customizable templates.
  • Home
  • TECH TRAINING
  • IT CONSULTING
  • IT RECRUITING
  • OUR CLIENTS
  • Contact
  • Blog