CASE Statement in SQL Server is similar to SWITCH CASE in C# or other object oriented programing language.

Note : When we use CASE Statement in SQL Server?
To avoid multiple if condition we use the CASE Statement.
We can't use the if condition in select statement where we can use the CASE statement.


We always use select statement while writing CASE statement. CASE statement always end with END keyword.


Syntax for CASE Statement

SELECT CASE WHEN < condition 1>
THEN < statement 1>
WHEN < condition 1>
THEN < statement 2>
ELSE < else statement>
END

1) Example

Below is the example of multiple CASE statement.

Example

DECLARE @UserAge INT = 42 --Set user agent is 42

SELECT CASE WHEN @UserAge <= 18
   THEN 'Use age less than 18'
   WHEN @UserAge <= 40
   THEN 'Use age between 18 and 60'
   WHEN @UserAge <= 60
   THEN 'Use age between 40 and 60'
   ELSE 'Use age greater than 60'
   END AS 'AgeGroup'

Output

multiple case statements example in sql server


Note : We always use th CASE statement with SELECT statement.


2) Example

CASE statement example with table columns.

Consider below result set and we have to categorize the student in three part
1) Marks less than 35 then Fail
2) Marks between 35 to 60 then Second Class
3) Marks between 61 to 100 then First Class

result set for multiple case condition

Example

SELECT StudentName,
    City,
    Marks,
   CASE WHEN Marks < 35 THEN 'Fail'
   WHEN Marks BETWEEN 35 AND 60 THEN 'Second Class'
   WHEN Marks BETWEEN 61 AND 100 THEN 'First Class'
   END AS 'Grade'

Output

multiple case statement sql server example


Note : CASE statement always return only one value.