Where IN a Comma delimited string

Question

I would like to retrieve certain users from a full list of a temp table #temptable. The query looks like this:

DECLARE @List varchar(max)
SELECT @List = coalesce(@List + ',','') + '''' + StaffCode + ''''
FROM tblStaffs

SELECT UserName
FROM #temptable
WHERE #temptable.StaffCode IN (@List)

I can tell @List is in a right format:

'AAA','ABB','BBB','CCC','DDD','MMM'

And if I change it to

WHERE #temptable.StaffCode IN ('AAA','ABB','BBB','CCC','DDD','MMM')

It certainly works, then why not IN (@List)?

It would not work for `in ('''AAA'',''BBB''')` which is really what is happening; I believe you'll need to parse @List into a small temp table and join against it. — johnjps111, May 28 '15 at 16:55
Thank you for pointing this out. This is really what I want to know. And one way to solve this problem is to use dynamic sql as answered by Eric from previous question: http://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function — Weihui Guo, May 28 '15 at 17:47

score 12 · Accepted Answer · edited Jun 02 '15 at 17:09

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.

@Zerubbabel - Its just a example you need to create a split string function. I have given you a link also from there you can create a split string function. — Pரதீப், May 28 '15 at 17:23

score 5 · Answer 2 · answered May 28 '15 at 17:08

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))

score 1 · Answer 3 · answered May 28 '15 at 17:04

1

I would recommend that you not use a comma-delimited string at all. Consider a semi-join instead:

select [Temp].[UserName] 
from 
    #temptable [Temp]
where
    exists (select 1 from [tblStaffs] where [tblStaffs].[StaffCode] = [Temp].[StaffCode]);

answered May 28 '15 at 17:04

Joe Farrell

3,502
1
15
25

1

What if the @List is an sproc or function parameter? – Glass Cannon Aug 07 '19 at 10:07
@GlassCannon I only made this suggestion because the example in the OP did not have `@List` as a sproc parameter, but as something declared and populated immediately before it was used. If it _were_ a sproc parameter, and if it were permissible to change the signature of the sproc, then I would suggest a table-valued parameter rather than a comma-delimited string. If for whatever reason the sproc's parameters cannot be modified, then (and only then) would I consider a string-splitting function as described in the accepted answer. – Joe Farrell Aug 08 '19 at 04:04

Where IN a Comma delimited string

3 Answers3

Linked