Can we pass Comma Separated List in SQL as a parameter

Question

My Code:

declare @text as nvarchar(max)
set @text=N'''ABC'',''XYZ'',''MNO'',''PQR'''
print @text
select * from table where column in(@text)  --Line1
select * from table where column  in('ABC','XYZ','MNO','PQR') --Line2

Executing Line1 and Line2 gives different results. Line2 is working fine but not able to execute Line1. Can anyone help me out in the same. I want to execute Line1, as the list will be my parameter into Stored Procdure.

you can do it with dynamic SQL. Use `exec('your query string')` — juergen d, Aug 20 '16 at 10:58
Consider a table-valued parameter instead of a delimited string: https://msdn.microsoft.com/en-us/library/bb510489.aspx. — Dan Guzman, Aug 20 '16 at 11:42
Possible duplicate of [Passing a varchar full of comma delimited values to a SQL Server IN function](http://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function) — Tab Alleman, Sep 30 '16 at 13:39

score 2 · Answer 1 · answered Aug 20 '16 at 11:07

You can. But you can't use IN for the behavior you intend. So, one method is:

select t.*
from table
where ',' + @text + ',' like '%,' + column + ',%' ;

Another method is to use dynamic SQL along with exec.

Another is to use a split() funciton:

with vals(val) as (
      select *
      from dbo.split(@text, ',')
     )
select t.*
from table t
where t.column in (select v.val from vals v);

You can Google "SQL Server split" to find the code for a split() function.

There are other ways to calculate vals as a table, including recursive CTEs and XML processing.

score 1 · Answer 2 · answered Aug 20 '16 at 11:18

I use this when dealing with SSRS reports. Pass the delimited string and split into a temp table, which you can then join on or use in an IN clause.

DECLARE @text NVARCHAR(20) = 'test;parameter;here'

SELECT Col INTO #text FROM dbo.GetTableFromDelimitedString(@text, ';')

SELECT * FROM table WHERE col IN (SELECT Col FROM #text)

Code for this function:

CREATE FUNCTION dbo.GetTableFromDelimitedString
    (
     @str VARCHAR(8000),
     @delimiter CHAR(1)
    )
RETURNS @t TABLE (Col VARCHAR(50))
AS
    BEGIN
        DECLARE @s VARCHAR(100),
            @l INT,
            @cnt INT;
        SET @l = LEN(@str);
        SET @s = '';
        SET @cnt = 1;
        WHILE @cnt <= @l
            BEGIN
                IF SUBSTRING(@str, @cnt, 1) = @delimiter
                    BEGIN
                        INSERT  INTO @t
                                (Col)
                        VALUES  (@s);
                        SET @s = '';
                    END;
                ELSE
                     BEGIN
                        SET @s = @s + SUBSTRING(@str, @cnt, 1);
                    END;
                SET @cnt = @cnt + 1;
            END;

---insert last one
        INSERT  INTO @t
                (Col)
        VALUES  (@s);
        RETURN; 
    END;

score 0 · Answer 3 · answered Aug 20 '16 at 11:36

Another possible solution without the use of any split function and a Sargable solution would be something like.....

Declare @text nvarchar(max), @xml xml;

SET @text= N'ABC,XYZ,MNO,PQR';

SET @xml = N'<root><r>' + replace(@text, ',','</r><r>') + '</r></root>';

select * 
from table 
where column in(
                select r.value('.','varchar(max)') 
                from @xml.nodes('//root/r') as records(r)
                );

score 0 · Answer 4 · answered Feb 21 '18 at 08:38

CREATE TABLE t 
  ( 
     id   INT, 
     col1 VARCHAR(50) 
  ) 

INSERT INTO t 
VALUES     (1, 
            'param1') 

INSERT INTO t 
VALUES     (2, 
            'param2') 

INSERT INTO t 
VALUES     (3, 
            'param3') 

INSERT INTO t 
VALUES     (4, 
            'param4') 

INSERT INTO t 
VALUES     (5, 
            'param5') 

DECLARE @params VARCHAR(100) 

SET @params = ',param1,param2,param3,' 

SELECT * 
FROM   t 
WHERE  Charindex(',' + Cast(col1 AS VARCHAR(8000)) + ',', @params) > 0

find the working Fiddle http://sqlfiddle.com/#!3/71c5d/1

Can we pass Comma Separated List in SQL as a parameter

4 Answers4