Passing a varchar full of comma delimited values to a SQL Server IN function

Question

Duplicate of
Dynamic SQL Comma Delimited Value Query
Parameterized Queries with Like and In

I have a SQL Server Stored Procedure where I would like to pass a varchar full of comma delimited values to an IN function. For example:

DECLARE @Ids varchar(50); SET @Ids = '1,2,3,5,4,6,7,98,234';  SELECT *  FROM sometable  WHERE tableid IN (@Ids); 

This does not work of course. I get the error:

Conversion failed when converting the varchar value '1,2,3,5,4,6,7,98,234' to data type int.

How can I accomplish this (or something relatively similar) without resorting to building dynamic SQL?

Answer 1

Of course if you're lazy like me, you could just do this:

Declare @Ids varchar(50) Set @Ids = ',1,2,3,5,4,6,7,98,234,'  Select * from sometable  where Charindex(','+cast(tableid as varchar(8000))+',', @Ids) > 0 

Answer 2

Don't use a function that loops to split a string!, my function below will split a string very fast, with no looping!

Before you use my function, you need to set up a "helper" table, you only need to do this one time per database:

CREATE TABLE Numbers (Number int  NOT NULL,     CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] DECLARE @x int SET @x=0 WHILE @x<8000 BEGIN     SET @x=@x+1     INSERT INTO Numbers VALUES (@x) END 

use this function to split your string, which does not loop and is very fast:

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 @ParsedList table (     ListValue varchar(500) ) AS BEGIN  /** Takes the given @List string and splits it apart based on the given @SplitOn character. A table is returned, one row per split item, with a column name "ListValue". This function workes for fixed or variable lenght items. Empty and null items will not be included in the results set.   Returns a table, one row per item in the list, with a column name "ListValue"  EXAMPLE: ---------- SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')      returns:         ListValue           -----------         1         12         123         1234         54321         6         A         *         |||         B          (10 row(s) affected)  **/    ---------------- --SINGLE QUERY-- --this will not return empty rows ---------------- INSERT INTO @ParsedList         (ListValue)     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!=''    RETURN  END --Function FN_ListToTable 

you can use this function as a table in a join:

SELECT     Col1, COl2, Col3...     FROM  YourTable         INNER JOIN FN_ListToTable(',',@YourString) s ON  YourTable.ID = s.ListValue 

Here is your example:

Select * from sometable where tableid in(SELECT ListValue FROM dbo.FN_ListToTable(',',@Ids) s)