Using the SQL CASE Statement
Posted by Rhonda Tipton on August 23, 2007
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
1: CASE expression
2: WHEN value1 THEN result1
3: WHEN value2 THEN result2
4: …
5: WHEN valueN THEN resultN
6:
7: ELSE elseResult
8: END AS
–
Example
1: CREATE TABLE [dbo].[Foo](
2: [ID] [int] IDENTITY(1,1) NOT NULL,
3: [xRefID] [varchar](20) NOT NULL,
4: [fullName] [varchar](40) NOT NULL,
5: [gender] [varchar](50) NULL,
6: [favoriteTVShow] [varchar](50) NULL,
7: [favoriteMovie] [varchar](50) NULL,
8: [favoriteBlog] [varchar](50) NULL)
9:
10: INSERT INTO Foo (xRefID, fullName, gender, favoriteTVShow, favoriteMovie, favoriteBlog)
11: VALUES (1200, ‘Rhonda Tipton’,‘F’,‘Heroes~Dexter~Lost’,‘Clerks~Chasing Amy~Resident Evil’,‘ Hanselman.com~ScottGu.com’)
12:
13: INSERT INTO Foo (xRefID, fullName, gender, favoriteTVShow, favoriteMovie, favoriteBlog)
14: VALUES (1201, ‘Bob Tipton’,‘M’,‘Heroes~BSG~SG1′,‘Saving Private Ryan~Star Wars’,‘The Wood Whisperer~Hanselman.com’)
15:
16: SELECT * FROM Foo
17:
18: SELECT xRefID, fullName,
19: CASE gender
20: WHEN ‘M’ THEN ‘Male’
21: WHEN ‘F’ THEN ‘Female’
22: ELSE ‘Unknown’
23: END AS gender,
24: favoriteTVShow, favoriteMovie, favoriteBlog
25: FROM Foo
26:
–
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!!!
