A Simple Use of Case Statement in SQL Server 2012

This article will give you an idea of how to use a Case expression in SQL Server.

In this article, I would like to show the most commonly used case expression in SQL Server. CASE is the special scalar expression or conditional statement in the SQL language which returns a single value based on the evaluation of a statement. Case statements can be used in Select and Where clauses and even an Order By clause. A Case expression is mostly used in SQL stored procedures or as a formula for a particular column, which optimizes the SQL statements. So let's take a look at a practical example of how to use a case statement in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.
 
We'll start by walking through a simple case statement. The Case statement can be used in two forms in SQL Server:
  1. Case statement with simple expression.
  2. Case statement with comparison or searched expression.

Case statement with simple expression

 
The Simple Case expression checks only for equivalent values and cannot contain Boolean expressions. A Simple Case Expression looks for the first expression in the list of all when the clause that matches expression_1 and evaluates the corresponding when clause. If there is no match, then the else clause is evaluated. The general syntax of the Simple CASE expression is:
 
CASE expression
WHEN exp_1 THEN result_1
[WHEN exp_2 THEN result_2]
[..................]
[WHEN exp_n THEN result_n]
[ELSE expression]
END
 
Example
  1. DECLARE @Name varchar(50)  
  2. SET @Name = 'Rohatash'  
  3. SELECT  
  4. Case  @Name  
  5.  WHEN  'Deepak'  THEN  'Name Deepak'  
  6.  WHEN  'Manoj' THEN  'Name Found Manoj'  
  7.  WHEN  'Rohatash' THEN   'Name Found Rohatash'  
  8. ELSE 'Name not Found'  
  9. END  
Output
 
Case statement in SQL Server 
 
Now replace the name Rohatash kumar in place of Rohatash.
 
Example
  1. DECLARE @Name varchar(50)  
  2. SET @Name = 'Rohatash'  
  3. SELECT  
  4. Case  @Name  
  5.  WHEN   'Deepak'  THEN  'Name Found Deepak'  
  6.  WHEN ' Manoj' THEN  'Name Found Manoj'  
  7.  WHEN  'Rohatash Kumar' THEN   'Name Found Rohatash'  
  8. ELSE 'Name not Found'  
  9. END  
Output
 
 
 

Case statement with comparison or searched expression

 
The Searched Case expression contains Boolean expressions or comparison operators. In the following example we will see how to use a searched expression with comparison operators.
 
The general syntax of the searched CASE expression is:
 
CASE
WHEN condition_1 THEN result_1
[WHEN condition_2 THEN result_2]
[..................]
[WHEN condition_n THEN result_n]
[ELSE expression]
[else result_n]
END
 
Creating a table in SQL Server
  1. create table student  
  2. (  
  3. stu_id int,  
  4. stu_name varchar(20),  
  5. marks int  
  6. );  
Now inserting rows values into the student table:
  1. Insert into student values('101','ravi','65');  
  2. Insert into student values('102','sumit','32');  
  3. Insert into student values('103','rekha','76');  
  4. Insert into student values('104','Yong','34');  
  5. Insert into student values('105','Hem','78');  
The Student table looks like this:
 
 
 
In the above table students Id, Name and their marks are given. Now I want the following output with the help of a CASE statement:
 
Stu_Id
Stu_Name
Marks
Remarks
Grade
101
Ravi
65
Pass
A
102
Sumit
32
Fail
E
103
Rekha
76
Pass
A +
104
Yong
34
Pass
E
105
Hem
78
Pass
A+
 

Using CASE Expression

  1. Select Stu_Id,Stu_Name,Marks,  
  2. Case When Marks > 32 Then 'Pass' Else 'Fail'  
  3. End as Remarks,  
  4. Case When Marks >= 76 Then 'A+'     
  5.         When Marks >= 65 Then 'A'  
  6.         When Marks < 35 Then 'E'   
  7. End as Grade  
  8. From Student  
Output
 
Using CASE Expression 
 

Case Statement with where condition

  1. Select Stu_Id,Stu_Name,Marks,  
  2. Case When Marks > 32 Then 'Pass' Else 'Fail'  
  3. End as Remarks,  
  4. Case When Marks >= 76 Then 'A+'     
  5.         When Marks >= 65 Then 'A'  
  6.         When Marks < 35 Then 'E'   
  7. End as Grade  
  8. From Student where marks< 35  
Output
 
Case Statement with where condition 
 

Case Statement with Order by clause

  1. Select Stu_Id,Stu_Name,Marks,  
  2. Case When Marks > 32 Then 'Pass' Else 'Fail'  
  3. End as Remarks,  
  4. Case When Marks >= 76 Then 'A+'     
  5.         When Marks >= 65 Then 'A'  
  6.         When Marks < 35 Then 'E'   
  7. End as Grade  
  8. From Student order by Marks  
Case Statement with Order by clause