Display all IN operator value, with status of 'available' or 'not available'

Question

I have list of string like ('222','333','43242','3242') which I have to used in a table dummy, let say my table dummy has only two values from this list ('222','333'), the data should be show as

dummyID    dummyStatus
222        Available
333        Available 
43242      Not Available
3242       Not Available

I have tried all possible query like

select dummyId, case when dummyId is null 'Available' else 'Not Available' end
from dummy
where dummyId in ('222','333','43242','3242')

but this doesn't work.

Answer 1

demo on db<>fiddle

1. You Case When ... Then End syntax is incorrect
2. You should use Desired table values like below

select v1.value, 
    case when dummyId is not null then 'Available' else 'Not Available' end as dummyStatus
from (
     values('222'),('333'),('43242'),('3242')
)v1(value)

left join (
     values('222'),('333') -- Let's say your table here
)dummy(dummyId) on v1.value = dummy.dummyId

Updated (Param is '(222,333,43242,3242)')

Demo on db<>fiddle

Let's say we have a strSplit function to help us split from string to table, then you can achieve in this way

declare @str varchar(200) = '(222,333,43242,3242)'

set @str = REPLACE(REPLACE(@str,'(', ''), ')', '')


select v1.val, 
case when dummyId is not null then 'Available' else 'Not Available' end as dummyStatus
from strSplit(@str, ',')v1

left join (
     values('222'),('333') -- Let's say your table here
)dummy(dummyId) on v1.val = dummy.dummyId

Note: SQL Server 2016 and later have STRING_SPLIT Then You just do like this, demo on db<>fiddle

declare @str varchar(200) = '(222,333,43242,3242)'

set @str = REPLACE(REPLACE(@str,'(', ''), ')', '')

select v1.value, 
case when dummyId is not null then 'Available' else 'Not Available' end as dummyStatus
from STRING_SPLIT(@str, ',')v1

left join (
     values('222'),('333') -- Let's say your table here
)dummy(dummyId) on v1.value = dummy.dummyId

Output