We provide IT Services

Register Now Click On

Read More Tutorials Click On

Post Page Advertisement [Top]

 


CASE statement in SQL : -

The case statement in SQL returns a value on a specified condition. We can use a Case statement in select queries along with Where, Order By, and Group By clause. 


It can be used in the Insert statement as well. In this article, we would explore the CASE statement and its various use cases.


Suppose you have a table that stores the Employee ID for all Employee in a IT Company. You want to get Employee Name for a particular Employee ID.


First Of All We Create A Employee table and store the data in Employee table.


CREATE TABLE tbl_employee

  (

    empid        INT PRIMARY KEY IDENTITY(1, 1),

     employeename VARCHAR(500),

     age          INT,

     gender       CHAR,

     salary       FLOAT,

     phone        VARCHAR(11),

     address      VARCHAR(500),

     countrycode  VARCHAR(10),

     designation  VARCHAR(100),

     create_date  DATETIME,

     update_date  DATETIME

  )

INSERT INTO tbl_Employee(EmployeeName,Age,Gender,Salary,Phone,Address,CountryCode,

Designation,Create_Date,Update_Date)

 VALUES('Devesh',26,'M',5000,9718400022,'Noida','IN','Director',GETDATE(),GETDATE())

INSERT INTO tbl_Employee(EmployeeName,Age,Gender,Salary,Phone,Address,CountryCode,

Designation,Create_Date,Update_Date)

VALUES('Neelam',15,'F',9000,9718450022,'Albama','US','Doctor',GETDATE(),GETDATE())

INSERT INTO tbl_Employee(EmployeeName,Age,Gender,Salary,Phone,Address,CountryCode,

Designation,Create_Date,Update_Date)

VALUES('Pankaj',18,'M',5500,9718100022,'Toronto','CA','Teacher',GETDATE(),GETDATE())

INSERT INTO tbl_Employee(EmployeeName,Age,Gender,Salary,Phone,Address,CountryCode,

Designation,Create_Date,Update_Date)

VALUES('Manish',35,'M',15000,9714500022,'Manchester','UK','HR',GETDATE(),GETDATE())

INSERT INTO tbl_Employee(EmployeeName,Age,Gender,Salary,Phone,Address,CountryCode,

Designation,Create_Date,Update_Date)

VALUES('Priya',20,'F',25000,9718780022,'Beijing','CH','Software Engg.',GETDATE(),GETDATE())


Look at the following example; We declared a variable @EmployeeId and specified value 1 for it. In Case statement, we defined conditions. Once a condition is satisfied, its corresponding value is returned.

DECLARE @EmployeeId INT

SET @EmployeeId = 2

SELECT CASE @EmployeeId

         WHEN 1 THEN 'Director'

         WHEN THEN 'Software Engg.'

         WHEN THEN 'Teacher'

         ELSE 'No Availble Designation name' 

       END AS  'Designation_Name' 





Example : -

SET @EmployeeId = 4

SELECT CASE @EmployeeId
         WHEN 1 THEN 'Director'
         WHEN THEN 'Software Engg.'
         WHEN THEN 'Teacher'
         ELSE 'No Availble Category name' 
       END AS 'Designation_Name' 



Case Statement with with comparison operator

following in this image you can see, we get designation with CASE statement as per specified condition.

Select E.EmployeeName, E.Salary,
CASE
WHEN Salary >=15000 AND Salary <=25000 THEN 'Director'
WHEN Salary >=9000 AND Salary <15000 THEN 'Manager'
WHEN Salary >=5000 AND Salary <9000 THEN 'Team Lead'
Else 'Director'
END AS Designation
from tbl_Employee AS E order by Salary desc


Case Statement with Order by clause : -

We can use Case statement with order by clause In SQL. And we use Order By clause with ascending or descending order then we find the record short form.

Consider for example; If we want to get sort result in the following method.

For Female employees, Employees salaries should come in descending order
For Male employees, Employees salaries Should come in ascending order

We can define this condition with a combination of Order by and with Case statement. In the following query, you can see we specified Order By and Case together. We defined sort conditions in case statement.

Select EmployeeName, Gender, Salary
 from tbl_Employee
 ORDER BY  CASE Gender
WHEN 'F' THEN Salary End DESC,
Case WHEN Gender='M' THEN Salary  
END


How to use case in where condition: -

     SELECT CASE T1.Empid
         WHEN 1 THEN T1.Designation
         WHEN 2 THEN T1.Designation
         WHEN 3 THEN T1.Designation
         ELSE 'No Availble Category name' 
       END AS 'DesignationName' FROM tbl_Employee AS T1 where T1.Empid= 2


No comments:

Post a Comment

| Designed by Rockprogrammer