Formatting a String for a SQL IN Clause

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!

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

Parameterizing a SQL IN clause

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.

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

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