Help with SQL Server query condition

I have the following query. but it gives me error in the in clause.

declare @lastName varchar(20)
set @lastName = 'Davis'

select *
from Table1 
where Date >= '2013-01-09'  
a开发者_C百科nd lastname in( 
    case 
    when @lastName = 'DAvis' THEN @lastName 
    else 'Brown','Hudson' end)

---

Something like this:

select *
from Table1 
where 
    Date >= '2013-01-09' and
    (
        (@lastName = 'Davis' and lastname = @lastName) or 
        (@lastName <> 'Davis' and lastname in ('Brown','Hudson'))
    )

---

Interesting. I think in this case specifically it is the "'Brown','Hudson'" that is causing the issue. You would probably be best not using a case statement at all.

The problem is I don't know of a way to return multiple values in a single case so you have to do like Alex said and use simple boolean logic. You can use a case statement in your where clause however, just not to return multiple values in a single case.

---

If you want to filter results by three names, use following query:

declare @lastName varchar(20)
set @lastName = 'Davis'

select * from Table1 

where Date >= '2013-01-09'  
and lastname in(
         'DAvis' ,'Brown','Hudson')