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

Wihtout using dynammid SQl you have to take the input variable and use a split function to put the data into a temp table and then join to that.

Answer 2

You can create a function that returns a table.

so your statement would be something like

select * from someable 
 join Splitfunction(@ids) as splits on sometable.id = splits.id

Here is a simular function.

CREATE FUNCTION [dbo].[FUNC_SplitOrderIDs]
(
    @OrderList varchar(500)
)
RETURNS 
@ParsedList table
(
    OrderID int
)
AS
BEGIN
    DECLARE @OrderID varchar(10), @Pos int

    SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
    SET @Pos = CHARINDEX(',', @OrderList, 1)

    IF REPLACE(@OrderList, ',', '') <> ''
    BEGIN
     WHILE @Pos > 0
     BEGIN
      SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
      IF @OrderID <> ''
      BEGIN
       INSERT INTO @ParsedList (OrderID) 
       VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
      END
      SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
      SET @Pos = CHARINDEX(',', @OrderList, 1)

     END
    END 
    RETURN
END

Answer 3

I've written a stored procedure to show how to do this before. You basically have to process the string. I tried to post the code here but the formatting got all screwy. Here is the example.

Answer 4

This works perfectly! The below answers are too complicated. Don't look at this as dynamic. Set up your store procedure as follows:

(@id as varchar(50)) as

Declare @query as nvarchar(max) set @query =' select * from table where id in('+@id+')' EXECUTE sp_executesql @query

Answer 5

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)

Answer 6

It's a very common question. Canned answer, several nice techniques:

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

Answer 7

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)+',', @Ids) > 0

Answer 8

Thanks, for your function I Used IT........................ This is my EXAMPLE

UPDATE [RD].[PurchaseOrderHeader] SET [DispatchCycleNumber] ='10' WHERE OrderNumber in(select * FROM XA.fn_SplitOrderIDs(@InvoiceNumberList))

CREATE FUNCTION [XA].[fn_SplitOrderIDs] ( @OrderList varchar(500) ) RETURNS @ParsedList table ( OrderID int ) AS BEGIN DECLARE @OrderID varchar(10), @Pos int

SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)

IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
    WHILE @Pos > 0
    BEGIN
            SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
            IF @OrderID <> ''
            BEGIN
                    INSERT INTO @ParsedList (OrderID) 
                    VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
            END
            SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
            SET @Pos = CHARINDEX(',', @OrderList, 1)

    END
END 
RETURN

END