Handling case statement subquery returning multiple rows select *
from temp tp
join a 
on    tp.id = a.id
where (  a.enddate   between @startdate and @endDate
      or a.startdate between @startdate and @endDate)
and   a.id1 in (case when (isnull(@id,''&lt;&gt;'')
                     then (select distinct id from #temp2)
                     else a.id1
                end)


The #temp2 query return multiple rows.Is that possible to achieve through case stament or else please do suugest the best possible way.

UDPATE: I am using the case statement as when the @id is null it should return all the records (a.id1=1,2,3,4,5,6)but when the @id=1,2,3 is not null it should return only those records which have the a.id1=1,2,3.
NOTE #1: Without knowing which RDMBS you're using I can't say if your proposed query will work or not (I'm leaning towards 'doubtful').

NOTE #2: I'm assuming the ( just before the isnull is not supposed to be there, otherwise I'm not sure what logic you're trying to implement, ie, you'll need to update your query with matching parens.

I'm thinking you'll want to pull the isnull test out to the top level and use an or to satisfy your conditional test, eg:

-- using '@id in (select ...)' construct

select *
from   temp tp
join   a 
on     tp.id = a.id

where  (  a.enddate   between @startdate and @endDate
       or a.startdate between @startdate and @endDate)

and    (   
            @id is not NULL
        or
           (@id is NULL and a.id1 in (select distinct id from #temp2) )
       )

-- or
-- using 'exists (select ...)' construct

select *
from   temp tp
join   a 
on     tp.id = a.id

where  (  a.enddate   between @startdate and @endDate
       or a.startdate between @startdate and @endDate)

and    (   
            @id is not NULL
        or
           (@id is NULL and exists(select 1 from #temp2 t where t.id=a.id1) )
       )