Optional Query with in a Query

[topmac]

Hi there,
May be this is a popular issue.... but this is the first time for me. Any
ideas are appreciated.

select *
from Table1 as t1
left outer join Table2 as t2
    on t1.id = t2.id
    and t2.type in (case when <condition>
                             then (1,2,3,4)
                         else 1
                    end)

how do I change the right hand side input in 'Where' clause, based on some conditions? There could be more one conditions in 'case' clause

case when <cond1> then
              (2,6,3,1)
     when <cond2> then
              (7,4,9,1)
     ...
     ...
     ...
     else 1   -- 1 is default option
end

Please help,

 

[vbkris]

i dont get u, is this wht u r looking for?
select *
from Table1 as t1
left outer join Table2 as t2
on t1.id = t2.id
and t2.type in (case when <condition>
then (1,2,3,4)
when <condition2>
then (1,2,3,4)
when <condition3>
then (1,2,3,4)
else 1
end)

simly keep adding as many "whens" as u wish!

Known is handfull, Unknown is worldfull

 

[SQLBill]

I think you will have to use Dynamic SQL for this. I don't use it myself, but there have been lots of threads about it on this site. Do a search, also check the FAQs to see if there is anything about using Dynamic SQL. Finally, look it up in the BOL.

-SQLBill

Posting advice: FAQ481-4875

 

[vbkris]

good point...

Known is handfull, Unknown is worldfull