Case Expression in SQL Server

In this code snippet we will have a look at Case Expression in SQL Server. CASE statement is used in conditions. We can modify records based on a certain condition using CASE Expression.

Syntax of CASE Expression:

CASE Expression
WHEN CONDITION1 THEN RESULT1
WHEN CONDITION2 THEN RESULT2
WHEN CONDITION3 THEN RESULT3
ELSE
RESULT4
END

Let us now see a practical example for the same. Write the following script in the SQL Server,

DECLARE @Student table
(
   ID int identity primary key,
   Name varchar(20),
   Gender Char(1),
   Marks int
)

INSERT INTO @Student
SELECT 'Nitin Tyagi','M',40
UNION
Select 'Jay Malik','M',50
UNION
SELECT 'Swati Singh','F',30

Select ID,Name,Marks,Gender=(CASE GENDER
WHEN 'M' THEN 'MALE'
WHEN 'F' THEN 'FEMALE'
END), Status=(CASE
WHEN MARKS>=40 THEN 'PASS'
WHEN MARKS<=40 THEN 'FAIL'
END)
from @Student

Here we have a table variable, Student, that has the basic details of the student. We capture the Name, Gender and marks of the student in the table. Now using CASE expression we will modify a few details and display it in the result set. First we will check if the Gender has n ‘M’ letter then it should be displayed as ‘Male’ otherwise ‘Female’.

Similarly we check for the status of the students. If the marks of the student are greater than 40 then we will display the status as ‘PASS’ but if the marks are less then 40 then it would be displayed as ‘FAIL’.

Let us now execute the preceding script and check the output.



We can see now that Gender is displayed as per the case expression and so is the status. This is how we can use CASE Expression.