Jul 22, 2016

Case statement in SQL Server

SQL Server gives us facility to fetch or modify records on condition basis. Suppose we have an “Order” table and we need to fetch records with conditions if user order price is less than 200 Rs it means user is “silver”, if price is greater than 200Rs and less than 500Rs it means user is Gold same like this if price is greater than 500Rs it means user is “Platinum” .

We can easily solve this problem using SQL Server.

Example:


CREATE TABLE #Order(
ID INT IDENTITY(1,1) PRIMARY KEY,
USR_NAME VARCHAR(100),
SKU VARCHAR(100),
QTY INT,
PRICE DECIMAL(18,2),
CREATED_DATE DATETIME
)

INSERT INTO #Order (USR_NAME,SKU,QTY,PRICE,CREATED_DATE)
SELECT 'User1','9876543210',2,200,GETDATE()
UNION ALL
SELECT 'User2','9876543210',2,300,GETDATE()
UNION ALL
SELECT 'User3','9876543210',2,400,GETDATE()
UNION ALL
SELECT 'User4','9876543210',2,550,GETDATE()

--Case statement
SELECT USR_NAME,PRICE,
(
            CASE
            WHEN PRICE<=200 THEN 'SILVER'
            WHEN PRICE>200 AND PRICE<=500 THEN 'GOLDEN'
            WHEN PRICE>500 THEN 'PLATINUM' ELSE 'NULL' END 
) AS USER_TYPE
FROM #Order

DROP TABLE #Order



 The basic formulations for CASE Expression

Simple Case Expression: Simple case expressions check one expression against multiple values. Simple case expressions allow only an equality check.

Example:


DECLARE @value INT=2
SELECT CASE @value WHEN 1 THEN 'ONE'
                                                   WHEN 2 THEN 'TWO'
                                                   WHEN 3 THEN 'THREE'
                                                   ELSE 'NOT MATCH'
                                                   END


Searched Case Expression: Searched Case Expression allows comparison operators. The simple CASE expression checks only for equivalent values and can’t contain Boolean expressions.

Example:



DECLARE @value INT=20
SELECT CASE WHEN @value<20 THEN 'SILVER'
                     WHEN @value>=20 AND @value<=50 THEN 'GOLDEN'
                     WHEN @value >50 THEN 'PLATINUM'
                     END