Formatting a String for a SQL IN Clause

Question

Hey Guys,

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!

Answer 1

Try single quotes around your item strings and split up. IN clauses for strings look like:

IN ('str1', 'str2')

And let us see some code. We'll be able to help better then. And also provide the exact error message you're getting if any. "doesn't work" won't help anyone a whole lot in diagnosing the issue.

Instead of using the param method for building your query you could just assemble the IN clause manually. You'll need to be careful about sql injection in that case and double any single quotes that might appear in the IN clause parameter list.

Answer 2

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

Parameterizing a SQL IN clause

Answer 3

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.

Answer 4

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

Answer 5

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