Why I can't use CASE statement in WHERE clause

Question

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' <br>
          and T2.Address1 = 'US')          
WHEN   'R3' THEN    <br>
          (T1.Sex = '' <br>
          and T2.Address1 = 'JAP')

Answer 1

0

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.

answered Dec 10 '10 at 07:05 AM

ThomasRushton ♦
29.4k ● 6 ● 9 ● 36

@Tom, that was what I done in my code but it seems a bit strange to look at using OR if you add too many condition between OR.

I want to change my code to CASE if that is possible.

you might say .... why I want to change it, code is still working okay with OR. :-)

Dec 10 '10 at 08:01 AM Leo

It is possible, but it's not recommended. Why not? Performance. CASE evaluates individual items, which Bad. SQL Server works best on sets of data.

If you must, try reading Jeff Smith's blog post on the subject at http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx

You also might want to re-read MS's documentation of the CASE clause - http://msdn.microsoft.com/en-us/library/ms181765.aspx - because it explains more about how it can and can't be used.

Oh, and don't call me Tom.

Dec 10 '10 at 09:38 AM ThomasRushton ♦

(comments are locked)

Answer 2

0

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

answered Dec 10 '10 at 10:07 AM

Phil Factor
3.2k ● 8 ● 9 ● 14

@Phil Factor This is a very good solution! Without this trick in the where clause, one would have to either use @ThomasRushton's approach or outsource the CASE activity to CTE (or sub-select) which will make the where clause simpler, but at the very high cost of needlessly pre-including potentially unwanted records and then filtering them out:

-- don't do it, this is just to reveal the 
-- superiority of Phil Factor's solution
with records (EmpName, Address1, IncludeIt) as
(
    SELECT 
        T1.EmpName, T2.Address1,
        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 AS IncludeIt
    FROM TABLE1 AS T1 INNER JOIN TABLE2 AS T2 
        ON T1.EmpNo = T2.EmpNo
    WHERE T1.EmpName LIKE @EmpFName + '%'
)
    SELECT EmpName, Address1
        FROM records
        WHERE IncludeIt = 1;

Dec 10 '10 at 11:05 AM Oleg

@thomasRuston Blooming brackets. Thanks for spotting it. I removed it.

Dec 10 '10 at 11:48 AM Phil Factor

@Phil Factor - No worries. I've now removed the comment mentioning it.

Dec 10 '10 at 01:23 PM ThomasRushton ♦

That's fantastic! Thank you Phil.

Dec 10 '10 at 01:55 PM Mark

(comments are locked)

Why I can't use CASE statement in WHERE clause

Follow this question

Related Questions