CASE statement inside a WHERE clause in SQL Server

Question

I am trying to add a CASE statement, but I need an OR within it and I am having a little trouble.

I'm not sure how I can do this, but I was hoping someone could help. Here is what I am trying to do:

SELECT *
  FROM Table1
 WHERE IsUpdate = CASE WHEN @Type = 'Yes' THEN 1 ELSE (0 OR 1) END

So basically, I want to select only the rows that have IsUpdate set to 1 when @Type = 'Yes', otherwise I want to select the rows where IsUpdate = 0 OR IsUpdate = 1. Any ideas?

Tim Schmelter · Accepted Answer · 2014-01-07T16:09:29.597

3

You don't need a CASE, i assume that the value can only be 0 or 1:

SELECT * FROM Table1
WHERE @Type <> 'Yes' OR IsUpdate = 1

If this is sitting in a stored-procedure it's probably better to use a If-Else instead of the parameter-check since above query is non-sargable so it might be inefficient on a large table.

edited Jan 07 '14 at 16:09

answered Jan 07 '14 at 16:02

Tim Schmelter

450,073
74
686
939

Well, it's most certainly been a long day. Thank you. – rjbogz Jan 07 '14 at 16:06

score 3 · Answer 2 · answered Jan 07 '14 at 16:06

The full where clause that matches your logic is:

where (@Type = 'Yes' and IsUpdate = 1) or
      (@Type <> 'Yes' and IsUpdate in (0, 1))

You can simplify this, if you know something about the values in the columns. For instance, if IsUpdate only takes on the values 0 and 1 (and not NULL):

where @Type <> 'Yes' or IsUpdate = 1

CASE statement inside a WHERE clause in SQL Server

2 Answers2