CODEWITHSUNDEEP
sql-server-2008-r2
Zerotoinfinity
Zerotoinfinity

Reputation: 6540

comma separated value in sql statement from a variable

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?

Upvotes: 2

Views: 2143

Answers (3)

moustafa malik
moustafa malik

Reputation: 78

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

Upvotes: 2

valex
valex

Reputation: 24144

SQLFiddle demo

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

Upvotes: 1

Murtuza Kabul
Murtuza Kabul

Reputation: 6514

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/

Upvotes: 0

Related Questions