Using the SQL CASE Statement

Posted by Rhonda

The common definition of CASE is as follows – Evaluates a list of conditions and returns one of multiple possible result expressions.

The CASE structure is included in just about every programming language (VB, C#, FoxPro, etc) and TSQL is no exception. In SQL Server, the SELECT and UPDATE commands can contain the CASE statement.

Syntax

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  WHEN valueN THEN resultN
 
  ELSE elseResult
END AS

–

Example

CREATE TABLE [dbo].[Foo](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [xRefID] [varchar](20) NOT NULL,
    [fullName] [varchar](40) NOT NULL,
    [gender] [varchar](50) NULL,
    [favoriteTVShow] [varchar](50) NULL,
    [favoriteMovie] [varchar](50) NULL,
    [favoriteBlog] [varchar](50) NULL)
 
INSERT INTO Foo (xRefID, fullName, gender, favoriteTVShow, favoriteMovie, favoriteBlog)
VALUES (1200, 'Rhonda Tipton','F','Heroes~Dexter~Lost','Clerks~Chasing Amy~Resident Evil',' Hanselman.com~ScottGu.com')
  
INSERT INTO Foo (xRefID, fullName, gender, favoriteTVShow, favoriteMovie, favoriteBlog)
VALUES (1201, 'Bob Tipton','M','Heroes~BSG~SG1','Saving Private Ryan~Star Wars','The Wood Whisperer~Hanselman.com')
 
SELECT * FROM Foo
 
SELECT xRefID, fullName,
       CASE gender
         WHEN 'M' THEN 'Male'
         WHEN 'F' THEN 'Female'
         ELSE 'Unknown'
       END AS gender,
       favoriteTVShow, favoriteMovie, favoriteBlog
FROM Foo
 

–

Result

–

Scott Mitchell has a very in-depth article on the Power of SQL Case Statements. I definitely suggest reading it for some much stronger examples of the CASE statement.

I also suggest an article titled Using CASE Expressions by Craig S. Mullins. This article also goes into quite a bit of detail.

–

Related Links
The Power of SQL CASE Statements – via Scott Mitchell
Using CASE Expressions – via Craig S. Mullins
Exploring the various uses of SQL’s CASE keyword – via TechRepublic
In T-SQL, use CASE…WHEN in place of IF…THEN – via TechRepublic

–

Have fun!!!