Where IN a Comma delimited string
Create some split string function and convert the comma separated values to rows then you can use the converted rows IN
clause
DECLARE @List VARCHAR(max)
SELECT @List = COALESCE(@List + ',', '') +StaffCode
FROM tblStaffs
SELECT UserName
FROM #temptable
WHERE #temptable.StaffCode IN (SELECT split_values
FROM dbo.Splitstring_function(@list))
Check here for various Split String function
If you dont want to create functions then you can also directly use the code instead of creating a new function(M.Ali's answer).
Another way of doing it is using dynamic query
.
Declare @List varchar(max), @sql nvarchar(max)
Select @List = coalesce(@List + ',','') + '''' + StaffCode + ''''
From tblStaffs
set @sql = '
Select UserName
From #temptable
Where #temptable.StaffCode IN ('+ @List + ')'
--print @sql
exec (@sql)
To debug the dynamic query always print
the dynamic sql before executing.
Because the Variable has a string which IN operator reads as 'AAA'',''ABB'',''BBB'
and it treats it as a single value.
In your query you should really use the query itself in the IN
operator something like....
Select UserName
From #temptable
Where #temptable.StaffCode IN (SELECT StaffCode From tblStaffs)
Anyway if there is a need to use variable and then read values inside the IN
operator from that variable you can do something like this....
DECLARE @List VARCHAR(1000);
Select @List = coalesce(@List + ',','') + StaffCode
From tblStaffs
SELECT *
From #temptable
Where #temptable.StaffCode IN (
SELECT t.c.value('.', 'VARCHAR(1000)')
FROM (
SELECT x = CAST('<t>' +
REPLACE(@List , ',', '</t><t>') + '</t>' AS XML)
) a
CROSS APPLY x.nodes('/t') t(c))