CASE statement in MS SQL Server is used to return different values in SELECT clause based on various Boolean conditions.

It is like Select – Case statement in C#.Net.

It has WHEN, THEN, ELSE clauses with End to terminate statement.

Syntax:

CASE [expression]
WHEN [Boolean expression/value] THEN [return value]
[ELSE [return value]]
END

e.g.
SELECT vchr_FirstName + ‘ ‘ + vchr_LastName AS ‘Name’,
CASE chr_IsEmployeeActive
WHEN ‘A’ THEN ‘Active’
WHEN ‘R’ THEN ‘Resigned’
END AS ‘Active’
FROM AE_EMPLOYEE

CASE statement in Order By-
e.g.
SELECT vchr_FirstName + ‘ ‘ + vchr_LastName AS ‘Name’, chr_IsEmployeeActive AS ‘Active’
FROM AE_EMPLOYEE
ORDER BY
CASE chr_IsEmployeeActive
WHEN ‘A’ THEN 2
WHEN ‘R’ THEN 1
END

We can write ‘Else’ statement if none of boolean expression is true.

e.g.
SELECT vchr_FirstName + ‘ ‘ + vchr_LastName AS ‘Name’,
CASE chr_IsEmployeeActive
WHEN ‘A’ THEN ‘Active’
WHEN ‘R’ THEN ‘Resigned’
ELSE ‘Unknown Flag’
END AS ‘Active’
FROM AE_EMPLOYEE

Pawan Kumar / Gauri D
Pawankkmr@hotmail.com