SQL SERVER – CASE Expression \ Case Statement with Example

Case statement in SQL Server checks a list of conditions
and returns one of the multiple possible result values. Either it compares a single expression or Evaluates a list of Boolean expressions in order to retrun the result

Case statement can be used either in the Data Retrieval Statement-SELECT or in the Data Manipulation Statements- UPDATE and DELETE or in the SET statements.

Syntax:

CASE expression
     WHEN value_expression1 THEN result_expression1 WHEN value_expression2 THEN result_expression2
     [ ELSE else_result_expression ]
END

Or

CASE
     WHEN Boolean_expression1 THEN result_expression1
     WHEN Boolean_expression2 THEN result_expression2
     [ ELSE else_result_expression ]
END

Let us see this through a simple example. Let us first create a table called Student with columns StudentId,  Exam1, Exam2 and Exam3 

Example :

Create table Student
(StudentId int, Exam1 int,Exam2 int,Exam3 int) 

Insert Into Student
Select 1,89,80,90
Union All
Select 2,69,83,95
Union All
Select 3,35,70,90
Union All
Select 4,49,82,60
Union All
Select 5,99,98,95

Now,let us have a glance on the data that we have inserted

Select * From Student

Now Let us see the gradings of the students depending upon the following criteria :

If Total Marks >= 250 then Grade = 'A'
If Total Marks between 200 and 250 then Grade = 'B'
If Total Marks between 100 and 200 then Grade = 'C'
If Total Marks < 100 then Grade = 'E'

Now In order to see the Grades, we can use the Case statement as follows:

Select StudentId,                                                           Case WHEN (Exam1+Exam2+Exam3) > 250 THEN 'A'
       WHEN (Exam1+Exam2+Exam3) between 200 and 250 THEN 'B'
       WHEN (Exam1+Exam2+Exam3) between 100 and 200 THEN 'C' 
        Else 'D'
END 'Grade'
From Student