Q:

SQL Server case statement embedded within In clause

I have written a case statement inside an IN clause. And I am getting the following error:

Subquery returned more than 1 value. This is not permitted when the
  subquery follows =, !=, <, <= , >, >= or when the subquery is used as
  an expression.

Here is the code:    
declare @Consultant as bigint
set @Consultant=0

select * from tProject where 
 tProject.ID in 
( 
Case
When @Consultant=0 then (select #t1.ProjectID from #t1)
else  (select #t1.ProjectID from #t1 inner join #t2 on #t1.ProjectID=#t2.ProjectID)
end
)

A:

Try this
declare @Consultant as bigint
set @Consultant=0

select * from tProject where 
( tProject.ID in (select #t1.ProjectID from #t1) and @Consultant=0)

or

(tProject.ID in 
(select #t1.ProjectID from #t1 inner join #t2 on 
#t1.ProjectID=#t2.ProjectID) and @Consultant<>0
)

or use IF
declare @Consultant as bigint
set @Consultant=0

If @Consultant=0 then
select * from tProject where 
tProject.ID in (select #t1.ProjectID from #t1)

else

select * from tProject where 
tProject.ID in 
(select #t1.ProjectID from #t1 inner join #t2 on 
#t1.ProjectID=#t2.ProjectID) 

