Case Statement/ Expression in SQL Server
Case Statement/ Expression in SQL server
Sometimes we need to get one result output from multiple possible outcomes based on the evaluation of some expression. This expression can be a simple case expression which compares an expression with a set of Expression/Values to determine the result or it can be a search case expression where it evaluates a list of Boolean expressions to determine the result.
We can use the case statement in Select statement, Update statement, in Where clause, In clause, Having Clause, in procedures and in User Defined Functions
Simple Case expression:-It compares an expression with a set of Expression/Values to determine the result.
Syntax:-
CASE input_expression
WHEN when input_expression 1 THEN result_expression1
WHEN when input_expression 2 THEN result_expression2 [ …n ]
ELSE else_result_expression
END
Example :-Suppose we have a stored procedure ‘usp_getcountryname’ which takes country code as input and returns the country name as output.
Create Procedure usp_getcountryname
(@I_countrycode nvarchar(10))
As
Begin
select case (@I_countrycode)
when ‘IN ‘ then ‘INDIA’
when ‘HK ‘ then ‘HONG KONG’
when ‘CN ‘ then ‘CHINA’
when ‘BR ‘ then ‘BRAZIL’
when ‘AU ‘ then ‘AUSTRALIA’
when ‘AF ‘ then ‘AFGHANISTAN’
else ‘No country found’ end as Countryname
End
when we execute this stored procedure using the command given below, it gives the result ‘INDIA’ . In the above example, we compare the input value with multiple values and based upon the comparison , it gives the result.
exec usp_getcountryname ‘IN’
Search case Expression:- In this case, it evaluates a list of Boolean expressions to determine the result.
Syntax:-
CASE
WHEN Boolean_expression1 THEN expression1
WHEN Boolean_expression2 THEN expression2
WHEN Boolean_expression2 THEN expression2 […n]
ELSE expression
END
Example:- Suppose we have a table called tbl_Employee which contains the employee information regarding their name, gender, age and salary.
Table creation script is given below:-
create table tbl_Employee(id int identity(1,1),Empname nvarchar(100),Gender nvarchar(5),Age int,Salary int)
Use the following Insert script to insert data in the table mentioned above:-
insert into tbl_Employee(Empname,Gender,Age,Salary)
select ‘Ram’,’M’,20,100000
union all
select ‘Neha’,’F’, 25,200000
union all
select ‘Praveen’,’M’,25,200000
union all
select ‘Garima’,’F’, 20, 100000
So, the initial data is given below:-
Select * from tbl_Employee
Suppose we have a requirement to update the salary of employees based on the following given conditions :-
1) If female and age less than 25 increase salary by 4 time
2) If female and age is greater or equal to 25 then increase salary by 3 times
3) If male and age is less than 25 then increase salary by 4.5 times
4) If male and age is greater or equal to 25 then increase salary by 2.5
In that case we can use the Search case expression to update employee salary
After the execution of the above update command, the data in the table becomes
Summary:- In this article , I tried to explain the Case expresion concept in SQL server with the help of examples.
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
Thanks Vivek, for clearing my doubts on stord procedure s.
How u personally solved my doubt on FB chat is very rare. Keep the good work up.