Please disable your adblock and script blockers to view this page
 

Examples to use CASE Expression in SELECT statement in SQL Server


Introduction: In this article I am going to explain how to use CASE (Transact-SQL) expression in SELECT statement in SQL SERVER with syntax and practical examples

Description: CASE statement in SQL is used to provide if-then-else type of logic as in other programming languages like C, C++, C# etc. Evaluates a list of conditions and returns one of multiple possible result expressions. It has two formats in terms of usages:

        Simple CASE expression, where we compare an expression to static values to determine the result.
        Searched CASE expression, where we compare an expression to one or more logical conditions to determine the result.
CASE can be used in statements such as SELECT, UPDATE, DELETE, SET, IN, WHERE, ORDER BY and HAVING etc.

Simple CASE Expression
A simple CASE expression checks one expression against multiple values in the order specified sequentially to determine the result. Within a SELECT statement, a simple CASE expression allows only an equality check. A simple CASE expression operates by comparing the first expression/condition to the expression in each WHEN clause for match. When and expressions is matched, the expression in the THEN clause will be returned as result.

Syntax is:
SELECT CASE Expression
WHEN Expression1 THEN Result1
WHEN Expression2 THEN Result2
...
ELSE ResultN
END

Or in simplified practical terms

SELECT CASE ("ColumnName")
  WHEN "Value1" THEN "Result1"
  WHEN "Value2" THEN "Result2"
  ...
  [ELSE "ResultN"]
  END
FROM "TableName";

Note: The ELSE clause is optional.

Searched CASE Expression
A searched CASE expression evaluates a set of Boolean expressions to determine the result and allows comparison operators, and the use of logic operators e.g. AND and/or OR between each Boolean expression.

Syntax is:
CASE
WHEN BooleanExpression1 THEN Result1
WHEN BooleanExpression2 THEN Result2
...
ELSE ResultN
END

Or in simplified practical terms

SELECT CASE
  WHEN "Condition1" THEN "Result1"
  WHEN "Condition2" THEN "Result2"
  ...
  [ELSE "ResultN"]
  END
FROM "TableName";

Note: The ELSE clause is optional.

Implementation: Let’s create a sample table and check the use of both CASE formats

--Create a table
CREATE TABLE tbCandidateResult
(
                CandidateId                       INT NOT NULL PRIMARY KEY IDENTITY(1,1),
                CandidateName               VARCHAR(100),
                TestName                           VARCHAR(20),
                MarksObtained                INT,
                Grade                                    CHAR(1)
)

--Insert some dummy data in the table created
INSERT INTO tbCandidateResult (CandidateName,TestName,MarksObtained,Grade) VALUES('Abhay','Asp.Net',40,'C')

INSERT INTO tbCandidateResult (CandidateName,TestName,MarksObtained,Grade) VALUES('Rajan','Asp.Net',76,'B')

INSERT INTO tbCandidateResult (CandidateName,TestName,MarksObtained,Grade) VALUES('Anuj','Asp.Net',12,'D')

INSERT INTO tbCandidateResult (CandidateName,TestName,MarksObtained,Grade) VALUES('Pankaj','Asp.Net',95,'A')

INSERT INTO tbCandidateResult (CandidateName,TestName,MarksObtained,Grade) VALUES('Kumal','Asp.Net',72,'B')

INSERT INTO tbCandidateResult (CandidateName,TestName,MarksObtained,Grade) VALUES('Mohit','Asp.Net',80,'B')

INSERT INTO tbCandidateResult (CandidateName,TestName,MarksObtained,Grade) VALUES('Arjun','Asp.Net',45,'C')

INSERT INTO tbCandidateResult (CandidateName,TestName,MarksObtained,Grade) VALUES('Rojan','Asp.Net',9,'D')

 --Check inserted data
SELECT * FROM tbCandidateResult

Query output will be as:
CandidateId
CandidateName
TestName
MarksObtained
Grade
1
Abhay
Asp.Net
40
C
2
Rajan
Asp.Net
76
B
3
Anuj
Asp.Net
12
D
4
Pankaj
Asp.Net
95
A
5
Kumal
Asp.Net
72
B
6
Mohit
Asp.Net
80
B
7
Arjun
Asp.Net
45
C
8
Rojan
Asp.Net
9
D

Suppose we want to evaluate the test performance from the grade achieved by each student based on the following condition:

Grade A : Outstanding
Grade B : Good
Grade C : Average
And for all other grades: Poor


--Simple CASE Expression
SELECT CandidateName,TestName,MarksObtained, CASE GRADE
WHEN 'A' THEN 'Outstanding'
WHEN 'B' THEN 'Good'
WHEN 'C' THEN 'Average'
ELSE 'Poor' END AS PerformanceRemarks
FROM tbCandidateResult

Query output will be as:
CandidateName
TestName
MarksObtained
PerformanceRemarks
Abhay
Asp.Net
40
Average
Rajan
Asp.Net
76
Good
Anuj
Asp.Net
12
Poor
Pankaj
Asp.Net
95
Outstanding
Kumal
Asp.Net
72
Good
Mohit
Asp.Net
80
Good
Arjun
Asp.Net
45
Average
Rojan
Asp.Net
9
Poor

Now suppose we want to evaluate the test performance from the marks obtained by each student based on the following condition:

90-100 marks : Outstanding
70-89 : Good
40-69 : Average
And for all other marks: Poor

--Searched CASE Expression
SELECT CandidateName,TestName,MarksObtained, CASE
WHEN MarksObtained >=90 AND MarksObtained <=100 THEN 'Outstanding'
WHEN MarksObtained >=70 AND MarksObtained <=89 THEN 'Good'
WHEN MarksObtained >=40 AND MarksObtained <=69 THEN 'Average'
ELSE 'Poor' END AS PerformanceRemarks
FROM tbCandidateResult

Query output will be as:
CandidateName
TestName
MarksObtained
PerformanceRemarks
Abhay
Asp.Net
40
Average
Rajan
Asp.Net
76
Good
Anuj
Asp.Net
12
Poor
Pankaj
Asp.Net
95
Outstanding
Kumal
Asp.Net
72
Good
Mohit
Asp.Net
80
Good
Arjun
Asp.Net
45
Average
Rojan
Asp.Net
9
Poor

Important Points

  • If no value/condition is found to be TRUE, then the CASE statement will return the value in the ELSE clause.
  • If the ELSE clause is omitted and no condition is found to be true, then the CASE statement will return NULL.
  • The CASE statement evaluates its expression/conditions sequentially and stops with the first condition whose condition is satisfied and will not evaluate the conditions further and returns the corresponding result
  • Nesting of Case expression is allowed up to 10 levels.

Now over to you:
"A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily.. ConversionConversion EmoticonEmoticon