Searched Case Expression in SQL

Searched Case Expression in SQL

The CASE expression has two formats:

The simple CASE expression compares an expression to a set of simple expressions to determine the result.

The searched CASE expression evaluates a set of Boolean expressions to determine the result.Like it Searches for condition and based on that it display Data

Example explaining Searched case expression

 In below Table we have Empsalary field it will check for Salary if Salary is 5000or 60000 it will double the salary in the output and if empname is null then it is returning salary as zero

--Table with employee Detail and Salary

CREATE TABLE [dbo].[Employee1](
[Empid] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [nvarchar](50) NULL,
[EmpSalary] [float] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employee1] ON
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'Amit', 5000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (2, N'Sumit', 6000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'Raj', 8000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (4, N'vijay', 9000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'suresh', 10000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (6, NULL, 5000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (7, NULL, 5000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (8, NULL, 5000)
SET IDENTITY_INSERT [dbo].[Employee1] OFF

select * from Employee1

--Query Displaying Empdetail with different conditions

select case when empsalary =5000 and EmpName IS not null then '10000'
when EmpSalary =6000 then '12000'
when empsalary=5000 and EmpName is null then '8000'
else empsalary end Salary
,EmpId,Empname from employee1

If you want to know about simple case expression with examples read my post:
http://sqlandssrssolutions.blogspot.in/2013/07/introduction-to-case-statement.html