Hello friends, in this article we are going to learn SQL Statement Equivalent to Ternary/Conditional Operator, Case Statement and Nested Case Statement.
The Case Statement is used for evaluating a multiple conditions and returns one of multiple possible result expressions (like an if else or ternary statement in C#).
So if a condition is true, it will stop further executing and return the result.
If not any conditions are true, it returns the value in the ELSE clause.
Syntax:
Simple CASE expression:
CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END
Searched CASE expression:
CASE WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END
input_expression: input_expression, is the expression evaluated when the simple CASE format is used.
WHEN when_expression: when_expression, is a simple expression to which input_expression is compared when the simple CASE format is used.
THEN result_expression: result_expression, is the expression returned when input_expression equals when_expression evaluates to TRUE.
ELSE else_result_expression: else_result_expression, is the expression returned if no comparison operation evaluates to TRUE.
WHEN Boolean_expression: Boolean_expression, is the Boolean expression evaluated when using the searched CASE format.
To understand simple CASE lets create a Student Table with some data as follows.
CREATE Table dbo.Student(StudentId int, StudentName varchar(200),Gender char)
Go
INSERT INTO dbo.Student VALUES(1,'Amit','M'),(2,'Johan','M'),(3,'Anju','F'),(4,'Rohan','M'),(5,'Amita','F')
INSERT INTO dbo.Student VALUES(6,'Amit','M'),(7,'Johan','M'),(8,'Anju','F'),(9,'Rohan','M'),(10,'Amita','F')
Go
SELECT StudentName,Gender FROM dbo.Student
Go
OUTPUT: StudentName Gender ------------- ------ Amit M Johan M Anju F Rohan M Amita F Amit M Johan M Anju F Rohan M Amita F (10 rows affected)
SELECT
StudentName,
CASE Gender
WHEN 'M' THEN 'Boy'
ELSE 'Girl'
END
AS Genger
FROM dbo.Student
Go
OUTPUT: StudentName Genger ------------ ------ Amit Boy Johan Boy Anju Girl Rohan Boy Amita Girl Amit Boy Johan Boy Anju Girl Rohan Boy Amita Girl (10 rows affected)
Take one more example to find grades on the basis of percentages as follows.
Create a table for StudentResult
CREATE table dbo.tbStudentResult
(
StudentID int,
Result decimal(5,2)
)
Go
Insert some record(s)
INSERT INTO dbo.tbStudentResult Values
(1,34),
(2,30),
(3,44),
(4,84),
(5,64),
(6,34),
(7,64),
(8,94),
(9,14)
Go
View for dbo.tbStudentResult
SELECT * FROM dbo.tbStudentResult
Go
OUTPUT: StudentID Result ----------- -------- 1 34.00 2 30.00 3 44.00 4 84.00 5 64.00 6 34.00 7 64.00 8 94.00 9 14.00 (9 rows affected)
SELECT StudentID,Result,
CASE WHEN Result>=91 THEN 'A'
WHEN Result>=61 THEN 'B'
WHEN Result>=41 THEN 'C'
ELSE 'F' END as Grade
FROM dbo.tbStudentResult
Go
OUTPUT: StudentID Result ----------- -------- 1 34.00 2 30.00 3 44.00 4 84.00 5 64.00 6 34.00 7 64.00 8 94.00 9 14.00 (9 rows affected)
Lets find category of students on basis of Grade like A,B Category I and C,F Category II using nested CASE Statement.
SELECT StudentID,Result,
CASE
WHEN Result>=91 THEN 'A'
WHEN Result>=61 THEN 'B'
WHEN Result>=41 THEN 'C'
ELSE 'F'
END Grade,
CASE
CASE
WHEN Result>=91 THEN 'A'
WHEN Result>=61 THEN 'B'
WHEN Result>=41 THEN 'C'
ELSE 'F'
END
WHEN 'A' THEN 'Category I'
WHEN 'B' THEN 'Category I'
ELSE 'Category II' END Category
FROM dbo.tbStudentResult
Go
OUTPUT: StudentID Result Grade Category ----------- ---------- ----- ----------- 1 34.00 F Category II 2 30.00 F Category II 3 44.00 C Category II 4 84.00 B Category I 5 64.00 B Category I 6 34.00 F Category II 7 64.00 B Category I 8 94.00 A Category I 9 14.00 F Category II (9 rows affected)
If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.
Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.