sql server - SQL Case Statement to Determine Filter -


i having issue here. trying use case statement determine filter use. ok use single values, not ok use select statement results.

 declare @singlevalue int = 0  select      t1.field1     , t1.field2     , t1.field3      tbltable1 t1     inner join tbltable2 t2 on t1.field1 = t2.field      t1.field2 in (select case when @singlevalue = 0 (select field1 tbltable3) else t1.field2 end)     , t2.field2 = (case when @singlevalue = 0 t2.field2 else @singlevalue end)  

once @singlevalue set value '123' working fine, moment when set value '0' (zero) returns error 'subquery returned more 1 value. not permitted when subquery follows =, !=, <, <= , >, >= or when subquery used expression.'

the problem start '(select field1 tbltable3)' in case statement. how can work around multiple values in case statement.

thanks.

maybe like:

declare @singlevalue int = 0  select      t1.field1     , t1.field2     , t1.field3      tbltable1 t1     inner join tbltable2 t2 on t1.field1 = t2.field      (         ( @singlevalue <> 0 )         or         ( t1.field2 in ( select field1 tbltable3) )     )     ,     (         ( @singlevalue = 0 )         or         ( t2.field2 = @singlevalue )     ) 

Comments