Why I am getting error in using Case statement in Where clause? Am I missing something? This is not an actual query, that is re-format the actual table and column name. Thanks. DECLARE @Dept VARCHAR(20) Delcare @EmpFName VARCHAR(50) SELECT T1.EmpName, T2.Address1 FROM TABLE1 AS T1 INNER JOIN TABLE2 AS T2 ON T1.EmpNo = T2.EmpNo WHERE (T1.EmpName LIKE @EmpFName + '%') AND CASE @Dept WHEN 'R1' THEN (T1.Sex 'M' and T2.Address1 = 'UK') WHEN 'R2' THEN (T1.Sex 'F'
and T2.Address1 = 'US') WHEN 'R3' THEN
(T1.Sex = ''
and T2.Address1 = 'JAP')
What you do is... SELECT ... WHERE (T1.EmpName LIKE @EmpFName + '%') AND ( ( @dept = 'R1' AND t1.Sex <> 'M' AND t2.Address1 = 'UK') OR ( @Dept = 'R2' AND T1.Sex <> 'F' AND t2.Address1 = 'US') OR ( @Dept = 'R3' AND...) ) (bits left out for you to do as your homework...) What I have done here is translate the CASE statement into a series of OR statements. However, you need to be careful with your bracketing of clauses when you do this, to ensure you get the right conditions going together. Check [MS's documentation on the precedence of operators]. :
I think that this is what you're trying to do. The problem with it will be that it will be dead slow for a large table but it may be your only way of doing it for all I know! I haven't been able to test it. I believe Celko came out with a slightly cleverer version of this a while back but I can't find it. DECLARE @Dept VARCHAR(20) DECLARE @EmpFName VARCHAR(50) SELECT T1.EmpName, T2.Address1 FROM TABLE1 AS T1 INNER JOIN TABLE2 AS T2 ON T1.EmpNo = T2.EmpNo WHERE T1.EmpName LIKE @EmpFName + '%' AND 1 = CASE WHEN 'R1' AND T1.Sex 'M' AND T2.Address1 = 'UK' THEN 1 WHEN @Dept='R2' AND T1.Sex 'F' and T2.Address1 = 'US' THEN 1 WHEN @Dept='R3' AND T1.Sex = '' AND T2.Address1 = 'JAP' THEN 1 ELSE 0 END