top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

    Get Free Article Updates

Facebook Login
Site Registration

Understanding Case Expression in SQL Server with Example

+1 vote
377 views

Sometimes, you required to fetch or modify the records based on some conditions. In this case, you may use cursor or loop for modify your records. In this situation Case expression is best alternative for Cursor/looping and also provides better performance.
You can use CASE expressions anywhere in the SQL Query like CASE expressions can be used with in SELECT statement, WHERE clauses, Order by clause, HAVING clauses,Insert, UPDATE and DLETE statements.

Format of CASE expression

The CASE expression has 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 with in 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 with in 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

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)
posted Dec 2, 2014 by Shivaranjini

  Promote This Article
Facebook Share Button Twitter Share Button Google+ Share Button LinkedIn Share Button Multiple Social Share Button


Related Articles

WITH x AS 
(
    SELECT * FROM MyTable
), 
y AS 
(
    SELECT * FROM x
)
SELECT * FROM y
READ MORE
Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...