SQL CASE Expression

SQL CASE evaluates a list of conditions and returns one of multiple possible result expressions. It helps to provide multiple if conditions type of logic in our SQL Query.

Lets look in to an example :
Consider a Table Orders with some columns

1
2
3
4
5
6
7
8
Declare Orders Table
(
    ID int identity (1, 1),
    Name Varchar(20),
    Quantity int,
    Code int,
    Value int 
)

Lets fetch records with columns ID, Name and Result where ID should be equal to 50. Here Result is obtained from Quantity, Code and value depending up on the Code. If Code is 0 Result is sum of Code+Quantity, If Code is 1 then Result is sum of Code+Value, else Result is Code+100 {if Code is neither 0 nor 1 then Result is Code+100} So depending up on the Code the Result value will be obtained.

Lets write a query which will do this

1
2
3
4
5
6
SELECT
ID, NAME, CASE WHEN CODE = 0 THEN CODE+QUANTITY
WHEN CODE = 1 THEN CODE+VALUE
ELSE CODE+100
END AS RESULT
WHERE ID = 50

The above example shows how CASE Expression can be used to attain the records as per the requirement by using Multiple IF conditions, in this ELSE Clause is optional we can have one more WHEN ( WHEN CODE != 0 OR CODE !=1 THEN CODE+100)

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.