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

Beginning Example

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

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:

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:

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:

Range Example

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

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:

Microsoft SQL Server 2005 - Standard Edition 5 CAL's Price: $1785 Buy It Now

SQL Server 2005 Enterprise Edition 1 CAL Current Price: $3000 Current Bids: 0

228-00683 MS SQL Server 2000 Standard 5 CAL Price: $375 Buy It Now

Microsoft SQL Server 2005 - Enterprise Edition 25 CAL's Price: $12999 Buy It Now

Microsoft SQL Server 2000 Standard 10 CAL 228-00684 NEW Price: $939 Buy It Now

Microsoft SQL Server 2008 R2 Enterprise w/25 Device Cal Price: $13049 Buy It Now