Why I can't use CASE statement in WHERE clause

Question

question

Leo asked Dec 10, '10

Why I can't use CASE statement in WHERE clause

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')

sql-server-2008 query case-statement

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton ♦♦ commented · Dec 10, 2010 at 07:12 AM

Indent code lines by four spaces. It even works in comments: SELECT * FROM T1 See? There are other formatting bits that work as well, but I can't remember them all. Shouldn't that be part of the FAQs?

2 ·

Leo commented · Dec 10, 2010 at 06:42 AM

how can you do that to make it proper sql format? I tried more then 5 times chaning the format before I post it. I can't managed to get it right. Changed whole format when I past into the box. ...

0 ·

Leo commented · Dec 10, 2010 at 07:59 AM

nice.thanks.

0 ·

Jeff Oresik commented · Dec 10, 2010 at 09:59 AM

You can't dynamically build a where clause using a case statement.

0 ·

Answer 1 · 2010-12-10T07:05:24Z

ThomasRushton answered Dec 10, '10

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][1]. [1]: http://msdn.microsoft.com/en-us/library/ms190276.aspx

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton ♦♦ commented · Dec 10, 2010 at 09:38 AM

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.

1 ·

Leo commented · Dec 10, 2010 at 08:01 AM

@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. :-)

0 ·

Answer 2 · 2010-12-10T10:07:09Z

Phil Factor answered Dec 10, '10

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

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg commented · Dec 10, 2010 at 11:05 AM

@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;

0 ·

Phil Factor commented · Dec 10, 2010 at 11:48 AM

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

0 ·

ThomasRushton ♦♦ commented · Dec 10, 2010 at 01:23 PM

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

0 ·

Mark commented · Dec 10, 2010 at 01:55 PM

That's fantastic! Thank you Phil.

0 ·

question