Formatting a String for a SQL IN Clause

Question

I need to format a string and pass it in as parameter for a SQL IN clause such as:

Select * from Table Where X In (@param1)

The literal string that is passed in as a param looks like this:

"Item1, Item2, Item3"

This does not seem to work. Any suggestions? Thanks!

You can't do that with parameter binding, unfortunately. If this is SQL Server you might try passing an xml document you can do a INSERT SELECT on. — Sean Bright, Feb 03 '10 at 17:56
I found the solution here: http://www.codeproject.com/KB/database/splitparameterstring.aspx , this does exactly what I needed, not sure if its the best approach — monO, Feb 03 '10 at 18:13

score 3 · Accepted Answer · edited May 23 '17 at 10:33

3

I believe what you want to do can be found here:

Parameterizing a SQL IN clause

edited May 23 '17 at 10:33

Community

1
1

answered Feb 03 '10 at 18:00

Nick Craver

594,859
130
1,270
1,139

score 2 · Answer 2 · answered Feb 03 '10 at 18:02

You can't use a string with the in operator and expect it to parse it. It will just compare the entire string with the values.

You would have to create the query dynamically, something like:

declare @sql varchar(4000)
set @sql = 'select * from Table where X in (' + @param1 + ')'
exec @sql

You would have to format the values that you send in the parameter as string literals:

"'Item1','Item2','Item3'"

Note that the string values has to escaped properly depending on what flavour of SQL you are using. This is very important, or your query is wide open for SQL injections.

score 0 · Answer 3 · answered Feb 03 '10 at 18:06

I'm not sure if this is the best answer but you can parse the param in a function that returns a table and select from that table as part of your in clause.

Ex.

DECLARE  @param1 varchar(50)
SET @param1 = 'Item1, Item2, Item3'

--CREATE a Table-valued FUNCTION to split param into a table(dbo.f_split_comma_separated_string)

Select * from Table Where X In (SELECT X FROM dbo.f_split_comma_separated_string(@param1))

score 0 · Answer 4 · edited May 23 '17 at 12:03

see my previous answer to this

this is the best source:

http://www.sommarskog.se/arrays-in-sql.html

create a split function, and use it like:

SELECT
    *
    FROM YourTable  y
    INNER JOIN dbo.splitFunction(@Parameter) s ON y.ID=s.Value

I prefer the number table approach

For this method to work, you need to do this one time table setup:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Once the Numbers table is set up, create this function:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(

    ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''

);
GO

You can now easily split a CSV string into a table and join on it:

select * from dbo.FN_ListToTable(',','1,2,3,,,4,5,6777,,,')

OUTPUT:

ListValue
-----------------------
1
2
3
4
5
6777

(6 row(s) affected)

Your can pass in a CSV string into a procedure and process only rows for the given IDs:

SELECT
    y.*
    FROM YourTable y
        INNER JOIN dbo.FN_ListToTable(',',@GivenCSV) s ON y.ID=s.ListValue

This will use an index on y.ID

Formatting a String for a SQL IN Clause

4 Answers4

Linked