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 2 THEN 'Software Engg.'
WHEN 3 THEN 'Teacher'
ELSE 'No Availble Designation name'
END AS 'Designation_Name'
No comments:
Post a Comment