x

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

asked Dec 10, 2010 at 06:32 AM in Default

Leo gravatar image

Leo
1.6k 54 56 58

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. ...
Dec 10, 2010 at 06:42 AM Leo

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?
Dec 10, 2010 at 07:12 AM ThomasRushton ♦
nice.thanks.
Dec 10, 2010 at 07:59 AM Leo
You can't dynamically build a where clause using a case statement.
Dec 10, 2010 at 09:59 AM Jeff Oresik
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Dec 10, 2010 at 07:05 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

@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, 2010 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, 2010 at 09:38 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Dec 10, 2010 at 10:07 AM

Phil Factor gravatar image

Phil Factor
3.9k 8 9 16

@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, 2010 at 11:05 AM Oleg
@thomasRuston Blooming brackets. Thanks for spotting it. I removed it.
Dec 10, 2010 at 11:48 AM Phil Factor
@Phil Factor - No worries. I've now removed the comment mentioning it.
Dec 10, 2010 at 01:23 PM ThomasRushton ♦
That's fantastic! Thank you Phil.
Dec 10, 2010 at 01:55 PM Mark
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1840
x371
x40

asked: Dec 10, 2010 at 06:32 AM

Seen: 13988 times

Last Updated: Dec 10, 2010 at 06:36 AM