SQL Server (2008) Pass ArrayList or String to SP for IN()

Question

Hi All,

I was wondering how I can pass either an ArrayList, List<int> or StringBuilder comma delimited list to a stored procedure such that I find a list of IDs using IN():

@myList varchar(50)

SELECT  *
FROM    tbl
WHERE   Id IN (@myList)

In C# I am currently building the list as a string which is comma delimeted; however when using nvarchar(50) for example, as the type for the param in the stored procedure - I get an error as it can't convert '1,2,3' to int which it expects between the IN().

Any ideas? Much appreciated.
Pete

Answer 1

I cannot provide you with the actual code, but I have solved this problem by writing a function that takes the varchar and returns a table of ints.

You can then do this ....

SELECT *
FROM tbl
WHERE Id IN (SELECT IntValue FROM dbo.CsvToInt(@myList))

Answer 2

You could use a User Defined function such as

CREATE function [dbo].[csl_to_table] ( @list nvarchar(MAX) )
RETURNS @list_table TABLE ([id] INT)
AS
BEGIN
    DECLARE  @index INT,
      @start_index INT,
      @id INT

    SELECT @index = 1 
    SELECT @start_index = 1
    WHILE @index <= DATALENGTH(@list)
    BEGIN

     IF SUBSTRING(@list,@index,1) = ','
     BEGIN

      SELECT @id = CAST(SUBSTRING(@list, @start_index, @index - @start_index ) AS INT)
      INSERT @list_table ([id]) VALUES (@id)
      SELECT @start_index = @index + 1
     END
     SELECT @index  = @index + 1
    END
    SELECT @id = CAST(SUBSTRING(@list, @start_index, @index - @start_index ) AS INT)
    INSERT @list_table ([id]) VALUES (@id)
    RETURN
END

Which accepts an nvarchar comma separated list of ids and returns a table of those ids as ints. You can then join on the returned table in your stored procedure like so -

DECLARE @passed_in_ids TABLE (id INT)

INSERT INTO @passed_in_ids (id)
  SELECT 
    id 
  FROM
    [dbo].[csl_to_table] (@your_passed_in_csl)

SELECT *
FROM 
myTable
INNER JOIN
@passed_in_ids ids
ON
myTable.id = ids.id

Answer 3

In SQL 2008 there are table-valued-parameters, that make a friendly alternative to parsing CSV; see here for an example.

Otherwise, another option is xml - the xml data type in SQL Server allows you to read this pretty easily (although it takes more transfer bytes).