T-SQL Tutorials - TSQLTutorials.com

CASE Statement


Introduction

The CASE Statement is used for evaluating a list of conditions to return a result. This T-SQL Expression begins with the keyword CASE. The CASE statement can be used in similar fashion to IF/ELSEIF, however the CASE statement is often much more readable in code. A CASE Statement can be used in the SELECT as well in other sections of a query such as WHERE, GROUP BY and ORDER BY. The CASE Statement has a few basic parts:

  • CASE [input_expression]
  •   WHEN   condition
         THEN result
         [..n]
  •   [ELSE] result
  • END
following the 'CASE' keyword you may put an optional input expression that can be used for evaluation. An example will be shown below.



Beginning Example

This example will use the following table: SELECT Name, Position, Salary
FROM Employees
Here is the resultset from the above SELECT query:

NamePositionSalary
Joe GrapeManager80000
John PlumSoftware Developer65000
Frank AppleSoftware Developer62000
Patty PineappleSoftware Developer60000
Judy PeachSoftware Developer50000
Jane OrangeProject Manager75000

Using the CASE expression we can evaluate the value of columns and create new return column with a different value. In this example we will return a silly name for some of the Positions from the above Employees table. SELECT
  Name,
  CASE
    WHEN [Position] = 'Manager'
       THEN'Bossman'
    WHEN [Position] = 'Software Developer'
       THEN 'Geek'
    ELSE
       'Other'
  END as SillyPositionName
FROM
  Employees

Here is the resultset from the above CASE Statement in the SELECT query from the Employees table:

NameSillyPositionName
Joe GrapeBossman
John PlumGeek
Frank AppleGeek
Patty PineappleGeek
Judy PeachGeek
Jane OrangeOther


Using CASE input_expression Example

This example will use the following table: SELECT Name, Position, Gender
FROM Employees
Here is the resultset from the above SELECT query:

NamePositionGender
Joe GrapeManager1
John PlumSoftware Developer1
Frank AppleSoftware Developer1
Patty PineappleSoftware Developer0
Judy PeachSoftware Developer0
Jane OrangeProject Manager0

Using the CASE expression we can evaluate the value of a column(s) and create new return column with a different value. In this example we will return the Gender description (0=Female,1=Male) for the Gender column from the above Employees table. SELECT
  Name,
  CASE [Gender]
    WHEN 0
       THEN 'Female'
    WHEN 1
       THEN 'Male'
    ELSE
       'Other'
  END as GenderDescription
FROM
  Employees

Here is the resultset from the above CASE Statement in the SELECT query from the Employees table:

NameGenderDescription
Joe GrapeMale
John PlumMale
Frank AppleMale
Patty PineappleFemale
Judy PeachFemale
Jane OrangeFemale


Range Example

This example will use the following table: SELECT Name, Position, Salary
FROM Employees
Here is the resultset from the above SELECT query:

NamePositionSalary
Joe GrapeManager80000
John PlumSoftware Developer65000
Frank AppleSoftware Developer62000
Patty PineappleSoftware Developer60000
Judy PeachSoftware Developer50000
Jane OrangeProject Manager75000

Using the CASE expression we can evaluate the value of columns and create new return column with a different value. In this example we will return a Salary Range for the employee Salaries from the above Employees table. SELECT
  Name,
  CASE
    WHEN [Salary] >= 75000
       THEN'High'
    WHEN [Salary] >= 60000
       THEN 'Middle'
    ELSE
       'Low'
  END as SalaryRange
FROM
  Employees

Here is the resultset from the above CASE Statement in the SELECT query from the Employees table:

NameSalaryRange
Joe GrapeHigh
John PlumMiddle
Frank AppleMiddle
Patty PineappleMiddle
Judy PeachLow
Jane OrangeHigh


Microsoft SQL Server 2014 Standard Edition Server w/20 CAL’s

Price: $641.99
Buy It Now
MSFT SQL Server 2016 Standard Edition x64 64bit | Original | 10CAL | 24hour sale

Price: $399.99
Buy It Now
Microsoft SQL Server 2012 Standard Server +10CAL License Key Factory Sealed Pack

Price: $795
Buy It Now
Microsoft SQL Server 2016 Enterprise w/ 5 CAL | Full Retail Media |

Price: $653.99
Buy It Now
MSFT SQL Server 2008 R2 Standard Edition 32/x64 | Full | 10CAL, Media, License

Price: $299.99
Buy It Now
Lenovo Microsoft SQL Server 2014 - License - 1 User CAL - OEM

Price: $242.08
Buy It Now







Copyright 2017 - TSQLTutorials.com