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.
CREATE TABLE #Order(
ID INT IDENTITY(1,1) PRIMARY KEY,
INSERT INTO #Order (USR_NAME,SKU,QTY,PRICE,CREATED_DATE)
WHEN PRICE<=200 THEN 'SILVER'
WHEN PRICE>200 AND PRICE<=500 THEN 'GOLDEN'
WHEN PRICE>500 THEN 'PLATINUM' ELSE 'NULL' END
) AS USER_TYPE
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.
DECLARE @value INT=2
SELECT CASE @value WHEN 1 THEN 'ONE'
WHEN 2 THEN 'TWO'
WHEN 3 THEN 'THREE'
ELSE 'NOT MATCH'
Searched Case Expression: Searched Case Expression allows comparison operators. The simple CASE expression checks only for equivalent values and can’t contain Boolean expressions.
DECLARE @value INT=20
SELECT CASE WHEN @value<20 THEN 'SILVER'
WHEN @value>=20 AND @value<=50 THEN 'GOLDEN'
WHEN @value >50 THEN 'PLATINUM'