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

Solution 1:

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

Parameterizing a SQL IN clause

Solution 2:

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@sqlvarchar(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.

Solution 3:

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@param1varchar(50)
SET@param1='Item1, Item2, Item3'--CREATE a Table-valued FUNCTION to split param into a table(dbo.f_split_comma_separated_string)Select*fromTableWhere X In (SELECT X FROM dbo.f_split_comma_separated_string(@param1)) 

Solution 4:

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
    INNERJOIN 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 10000IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSSJOIN sys.objects s2
ALTERTABLE Numbers ADDCONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

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

CREATEFUNCTION[dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNSTABLEASRETURN 
(

    ----------------
    --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
        INNERJOIN dbo.FN_ListToTable(',',@GivenCSV) s ON y.ID=s.ListValue

This will use an index on y.ID