SQL Server Case Expression: An Overview
Sometimes, you are required to fetch or modify the records based on some conditions. In this case, you may use a cursor or loop to modify your records. In this situation, Case expression is the best alternative for Cursor/looping and also provides better performance. SQL Server skills can boost your earning potential by 22% in the next 3 years. Enroll in our Free SQL Server Training Online with Certificate today!
You can use CASE expressions anywhere in the SQL Query. CASE expressions can be used with SELECT statements, WHERE clauses, Order by clause, HAVING clauses, Insert, UPDATE, and DELETE statements.
Format of CASE Expression
The CASE expression has the following two formats:
1. Simple CASE Expression
This compares an expression to a set of simple expressions to find the result. This expression compares an expression to the expression in each WHEN clause for equivalency. If the expression within the WHEN clause is matched, the expression in the THEN clause will be returned.
Syntax
CASE expression
WHEN expression1 THEN Result1
WHEN expression2 THEN Result2
ELSE ResultN
END
2. Searched CASE Expressions
This expression evaluates a set of Boolean expressions to find the result. This expression allows comparison operators, and logical operators AND/OR within each Boolean expression.
Syntax
CASE
WHEN Boolean_expression1 THEN Result1
WHEN Boolean_expression2 THEN Result2
ELSE ResultN
END
CASE Expression Example
CREATE TABLE dbo.Customer
(
CustID INT IDENTITY PRIMARY KEY,
FirstName VARCHAR(40) NOT NULL,
LastName VARCHAR(40) NOT NULL,
StateCode VARCHAR(20) NOT NULL,
PayRate money NOT NULL DEFAULT 0.00,
Gender VARCHAR(1) NOT NULL,
)
GO
INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender)
VALUES('Tejendra', 'Kumar', 'UP', 150.00,'M')
INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender)
VALUES('Jolly', 'Kapoor', 'MP', 50.00 ,'F')
INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender)
VALUES('Pavan', 'Kumar', 'MP', 200.00 ,'M')
INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender)
VALUES('Boby', 'Sharma', 'DL', 180.00 ,'F')
INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender)
VALUES('Asif', 'Khan', 'DL', 210.00 ,'M')
GO
SELECT * from Customer
Read More: SQL Server Interview Questions and Answers
SELECT statement with CASE Expressions
--Simple CASE expression:
SELECT FirstName, State=(CASE StateCode
WHEN 'MP' THEN 'Madhya Pradesh'
WHEN 'UP' THEN 'Uttar Pradesh'
WHEN 'DL' THEN 'Delhi'
ELSE NULL
END), PayRate
FROM dbo.Customer
-- Searched CASE expression:
SELECT FirstName,State=(CASE
WHEN StateCode = 'MP' THEN 'Madhya Pradesh'
WHEN StateCode = 'UP' THEN 'Uttar Pradesh'
WHEN StateCode = 'DL' THEN 'Delhi'
ELSE NULL
END), PayRate
FROM dbo.Customer
Update Statement with CASE Expression
-- Simple CASE expression:
UPDATE Customer
SET StateCode = CASE StateCode
WHEN 'MP' THEN 'Madhya Pradesh'
WHEN 'UP' THEN 'Uttar Pradesh'
WHEN 'DL' THEN 'Delhi'
ELSE NULL
END
-- Simple CASE expression:
UPDATE Customer
SET StateCode = CASE
WHEN StateCode = 'MP' THEN 'Madhya Pradesh'
WHEN StateCode = 'UP' THEN 'Uttar Pradesh'
WHEN StateCode = 'DL' THEN 'Delhi'
ELSE NULL
END
ORDER BY Clause with CASE Expressions
-- Simple CASE expression:
SELECT * FROM dbo.Customer
ORDER BY
CASE Gender WHEN 'M' THEN FirstName END Desc,
CASE Gender WHEN 'F' THEN LastName END ASC
-- Searched CASE expression:
SELECT * FROM dbo.Customer
ORDER BY
CASE WHEN Gender='M' THEN FirstName END Desc,
CASE WHEN Gender='F' THEN LastName END ASC
Having Clause with CASE Expression
-- Simple CASE expression:
SELECT FirstName ,StateCode,Gender, Total=MAX(PayRate)
FROM dbo.Customer
GROUP BY StateCode,Gender,FirstName
HAVING (MAX(CASE Gender WHEN 'M'
THEN PayRate
ELSE NULL END) > 180.00
OR MAX(CASE Gender WHEN 'F'
THEN PayRate
ELSE NULL END) > 170.00)
-- Searched CASE expression:
SELECT FirstName ,StateCode,Gender, Total=MAX(PayRate)
FROM dbo.Customer
GROUP BY StateCode,Gender,FirstName
HAVING (MAX(CASE WHEN Gender = 'M'
THEN PayRate
ELSE NULL END) > 180.00
OR MAX(CASE WHEN Gender = 'F'
THEN PayRate
ELSE NULL END) > 170.00)
Do you Know?
.NET is gaining popularity day by day, especially after the release of .NET 8. .NET 8 is not only a framework version but much more than that. It redefines the way software applications are built and deployed, enabling developers to meet the evolving demands of modern computing.
Full-Stack .NET Developers earn up to ₹35 LPA in India’s tech boom. Kickstart your high-paying career with our full stack .NET developer course today!
