CASE Expression 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. Stay tuned and stay connected for more technical updates."