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.

DMCA.com

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in Most Imp SQL Concepts, SQL Basic Concepts, SQL Server and tagged , . Bookmark the permalink.

One Response to Case Statement/ Expression in SQL Server

  1. 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.

Leave a Reply