Return all values from IN Clause on SQL Server 2000 -

is there way retrieve data in clause?

let's assume table got (id,name):

0 banana
1 mango
2 papaya
3 lemon

and query:

select * fruits name in (banana,mango,orange)

i want 'orange' return, empty id (since there's no register). how this?

you can't use in clause this. need target fruits table can outer join against.

select ...  (select 'banana' fruit union select 'mango' union select 'orange') f  left join fruits on fruits.name = f.fruit

or option 2 (as long list <= 8000 characters). create udf like 1 here (but using varchar(8000) instead of varchar(max)). use follows.

select ... dbo.fnsplitstringlist('banana,mango,orange') f  left join fruits on fruits.name = f.stringliteral

DR ode