comma separated value in sql statement from a variable

Question

I am getting comma separated value like this in a variable (let say variable name @listobj)

'abc' , 'xyz'

but when I am using below statement it is not giving me the correct result

SELECT * FROM someTable
Where column1 IN (@listobj)

but abc is present in the table.

Where I am doing it wrong?

This link discusses the problem in detail: http://www.williamrobertson.net/documents/comma-separated.html — guruprasath, Mar 20 '13 at 11:43
possible duplicate of [Parameterizing an SQL IN clause?](http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause) — icc97, Feb 04 '14 at 15:20

score 2 · Accepted Answer · edited Nov 01 '19 at 12:24

create a function that split the string to

    CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (items varchar(8000))     
as     
begin     
declare @idx int     
declare @slice varchar(8000)     

select @idx = 1     
    if len(@String)<1 or @String is null  return     

while @idx!= 0     
begin     
    set @idx = charindex(@Delimiter,@String)     
    if @idx!=0     
        set @slice = left(@String,@idx - 1)     
    else     
        set @slice = @String     

    if(len(@slice)>0)
        insert into @temptable(Items) values(@slice)     

    set @String = right(@String,len(@String) - @idx)     
    if len(@String) = 0 break     
end 
return     
end

then make call to the function SELECT * FROM someTable
Where column1 IN (dbo.Split(@listobj))

enter link description here

valex · Answer 2 · 2013-03-21T04:50:02.360

1

SQLFiddle demo

select * from someTable
where ','+LTRIM(RTRIM(@listobj))+',' LIKE '%,'+LTRIM(RTRIM(column1))+',%'

edited Mar 21 '13 at 04:50

answered Mar 20 '13 at 11:46

valex

23,966
7
43
60

this isn't working. When I doing hard coded value then it is working fine but not with a variable :( – Zerotoinfinity Mar 20 '13 at 12:47
@Zerotoinfinite: I think it it because `column1` or `@listobj` has spaces in the end. For example it they aren't VARCHAR() but CHAR(). I've changed SQL to handle this issue. – valex Mar 21 '13 at 04:51

score 0 · Answer 3 · edited May 23 '17 at 11:50

A classic question and the answer is no, you cannot use a parameter in this way. There are several workarounds though

One of which is to parse the value inside the stored procedure and dynamically generate sql inside the procedure to be execute later. However, this is not a good practice.

Refer to this question

How to pass a comma separated list to a stored procedure?

and also some good discussion on it here

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/1ccdd39e-8d58-45b2-9c21-5c4dbd857f95/

comma separated value in sql statement from a variable

3 Answers3