When we need to write conditions with case in the where clause
we can either write the first part or second part or both in a case expression.
a usual expression in the where clause should contain two parts
eg: WHERE ID=23
OR WHERE ID=@ID etc..
So here we can use the case expression for the first part or the second part
the following examples will guide you.
DECLARE @IsActive int
SET @IsActive=1
SELECT * FROM Employee
WHERE IsSponsor = CASE @IsActive
WHEN 1 THEN 1
WHEN 0 THEN 0
ELSE 0
END
in the above CASE is used in the second part
DECLARE @IsActive int
SET @IsActive=1
SELECT * FROM Employee
WHERE CASE
WHEN UserRole=1 AND IsSponsor=@IsActive THEN 1
WHEN UserRole=2 AND IsTrainee=@IsActive THEN 1
ELSE 0
END =1
in the above codes the first part becaomes either 1 or 0 depending on the conditions and then it is checking with “=1” which you can find after the END
you can Find END=1 there actually we are trying to make 0=1 or 1==1
then the below is an example for nested case when
DECLARE @IsActive int
SET @IsActive=1
SELECT * FROM Employee
WHERE CASE
WHEN UserRole=1
CASE
WHEN IsSponsor=@IsActive THEN 1
ELSE 0
END
WHEN UserRole=2
CASE
WHEN IsTrainee=@IsActive THEN 1
ELSE 0
END
ELSE 0
END=1
here also
the first part will make either 0 or 1 depending up on the conditions and then it is matched with =1 which you can find in the last line END =1
Another Examples
SELECT * FROM Orders
WHERE CustomerID=CASE @Var1 WHEN 'Customers' THEN @Var2 ELSE 0 END
OR EmployeeID=CASE @Var1 WHEN 'Employees' THEN @Var2 ELSE 0 END
SELECT * FROM Orders
WHERE 1 =
CASE @Var1
WHEN 'Customers' THEN CASE WHEN CustomerID=@Var2 THEN 1 ELSE 0 END
WHEN 'Employees' THEN CASE WHEN EmployeeID=@Var2 THEN 1 ELSE 0 END
END
Suggesions and replaies are welcome..
Happy Programming..
Rinosh K Sasidharan
http://www.rinosh.co.cc
http://www.rinoshsasidharan.wordpress.com