SQL SERVER – SQL CASE Statement Conditions Examples Syntax and Explanation

The SQL CASE statement is used as a conditional operator that considers a value or expression, examines it, and return values if matched. CASE is used to provide if-then-else type of logic or Switch Case type of logic to SQL. There are two formats: The first is a Simple CASE expression, where we compare an expression to static values. The second is a Searched CASE expression, where we compare an expression to one or more logical conditions. We can replace resource hungry Cursor/Looping SQL and can provide better performance using CASE statement.

One can use CASE expressions anywhere in the SQL Query like within SELECT statement, WHERE clause, Order by clause, HAVING clause,Insert, UPDATE and DELETE statements.

The CASE expression has two formats:
1. Simple CASE expression: Compare an expression to static values.
Simple CASE expression Syntax:

CASE input_expression 
     WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

2. Searched CASE expression: Compare an expression to one or more logical conditions.
Searched CASE expression Syntax:

CASE
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

Create the following table to do some Example:

CREATE TABLE [dbo].[Vendor](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [varchar](max) NULL,
	[LastName] [varchar](max) NULL,
	[CountryCode] [varchar](50) NULL,
	[Zone] [varchar](50) NULL,
	[commissionRate] [decimal](18, 2) NULL,
 CONSTRAINT [PK_Vendor] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO VENDOR VALUES('Shawpnendu','Bikash','BD','Asia',10)
INSERT INTO VENDOR VALUES('Bimalandu','Maloroy','IN','Asia',11)
INSERT INTO VENDOR VALUES('Kamal','Uddin','PK','Asia',11)
INSERT INTO VENDOR VALUES('Afsarul','Alam','AF','Asia',11)
INSERT INTO VENDOR VALUES('Einar','Ag','NO','Europe',9)
INSERT INTO VENDOR VALUES('Egil','Larse','NO','Europe',8)
INSERT INTO VENDOR VALUES('Carina','Tanenbarg','US','America',13)
INSERT INTO VENDOR VALUES('Per','Tanenbarg','US','America',13)

SELECT * FROM VENDOR

Example Table:
Vendor Table to check SQL Case Statement

Example 1: Using CASE Statement in SELECT Query Clause:

-- Simple CASE expression
SELECT FirstName,LastName,
CASE CountryCode
WHEN 'BD' THEN 'Bangladesh'
WHEN 'IN' THEN 'India'
WHEN 'PK' THEN 'Pakistan'
WHEN 'AF' THEN 'Afganistan'
WHEN 'NO' THEN 'Norway'
WHEN 'US' THEN 'United States'
END Country,Zone,CommissionRate
FROM Vendor 


-- Searched CASE expression
SELECT FirstName,LastName,
CASE 
WHEN CountryCode='BD' THEN 'Bangladesh'
WHEN CountryCode='IN' THEN 'India'
WHEN CountryCode='PK' THEN 'Pakistan'
WHEN CountryCode='AF' THEN 'Afganistan'
WHEN CountryCode='NO' THEN 'Norway'
WHEN CountryCode='US' THEN 'United States'
END Country,Zone,CommissionRate
FROM Vendor

Output:
Using Case statement in Select Query

Example 2: Using CASE Statement in SELECT Query Clause:

-- Simple CASE expression
SELECT 
CASE Zone
WHEN 'Asia' THEN FirstName+' '+LastName
ELSE LastName+' '+FirstName END Name,
CountryCode,Zone,CommissionRate
FROM Vendor 

-- Searched CASE expression
SELECT 
CASE
WHEN Zone='Asia' THEN FirstName+' '+LastName
ELSE LastName+' '+FirstName END Name,
CountryCode,Zone,CommissionRate
FROM Vendor

Output:
Using Case statement in Select Query 2

Using CASE Statement in WHERE Query Clause:

-- Simple CASE expression
SELECT * FROM Vendor
WHERE Zone=CASE Zone WHEN 'Asia' THEN 'Asia' END 

-- Searched CASE expression
SELECT * FROM Vendor
WHERE Zone=CASE WHEN Zone='Asia' THEN 'Asia' END

Output:
Using Case statement in Where Clause

Using CASE Statement in UPDATE SQL or Query:

-- Simple CASE expression
UPDATE Vendor
SET CountryCode=
CASE CountryCode
WHEN 'BD' THEN 'Bangladesh'
WHEN 'IN' THEN 'India'
WHEN 'PK' THEN 'Pakistan'
WHEN 'AF' THEN 'Afganistan'
WHEN 'NO' THEN 'Norway'
WHEN 'US' THEN 'United States'
ELSE CountryCode
END

-- Searched CASE expression
UPDATE Vendor
SET CountryCode=
CASE 
WHEN CountryCode='BD' THEN 'Bangladesh'
WHEN CountryCode='IN' THEN 'India'
WHEN CountryCode='PK' THEN 'Pakistan'
WHEN CountryCode='AF' THEN 'Afganistan'
WHEN CountryCode='NO' THEN 'Norway'
WHEN CountryCode='US' THEN 'United States'
ELSE CountryCode
END

Output:
Using Case statement in Update Query

Using CASE Statement in ORDER BY SQL or Query:

-- Simple CASE expression
SELECT
CASE Zone
WHEN 'Asia' THEN FirstName+' '+LastName
ELSE LastName+' '+FirstName END Name,
CountryCode,Zone,CommissionRate 
FROM Vendor
ORDER BY 
CASE Zone WHEN 'Asia' THEN FirstName+' '+LastName 
ELSE LastName+' '+FirstName END

-- Searched CASE expression
SELECT
CASE 
WHEN Zone='Asia' THEN FirstName+' '+LastName
ELSE LastName+' '+FirstName END Name,
CountryCode,Zone,CommissionRate 
FROM Vendor
ORDER BY 
CASE Zone WHEN 'Asia' THEN FirstName+' '+LastName 
ELSE LastName+' '+FirstName END

Output:
Using Case statement in Order by Query

Using CASE Statement in HAVING Clause:

-- Simple CASE expression
SELECT Zone, AVG(CommissionRate) 'Commission with Markup' FROM Vendor
GroUP BY Zone
HAVING AVG(CommissionRate)<=
CASE Zone
WHEN 'Asia' THEN 15 ELSE 10 END

-- Searched CASE expression
SELECT Zone, AVG(CommissionRate) 'Commission with Markup' FROM Vendor
GroUP BY Zone
HAVING AVG(CommissionRate)<=
CASE 
WHEN Zone='Asia' THEN 15 ELSE 10 END

Output:
Here I have added markup for Asia up to 15 but for others no markup, strict to 10. For such type of query we have no alternatives except CASE statement.
Using Case statement in HAVING Clause

Hope you gained a lot of confidence on using CASE statement in different cases.

Leave a Reply

Your email address will not be published. Required fields are marked *

AlphaOmega Captcha Classica  –  Enter Security Code