I'm wondering the way i can pass either an ArrayList, List<int> or StringBuilder comma delimited list to some saved procedure so that I've found a listing of IDs using IN():

@myList varchar(50)

SELECT  *
FROM    tbl
WHERE   Id IN (@myList)

In C# I'm presently building their email list like a string that is comma delimeted however, if using nvarchar(50) for instance, because the type for that param within the saved procedure - I recieve a mistake because it can't convert '1,2,3' to int so it needs between your IN().

Any ideas? Much appreciated.
Pete

You could utilize a person Defined function for example

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 listing of ids and returns a table of individuals ids as ints. After that you can join around the came back table inside your saved 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

In SQL 2008 you will find table-valued-parameters, which make an amiable option to parsing CSV see for an example.

Otherwise, an alternative choice is xml - the xml data key in SQL Server enables you to definitely look at this pretty easily (even though it takes more transfer bytes).

I am unable to offer the actual code, however i have solved this issue by writing a function that can take the varchar and returns a table of ints.

After that you can do that ....

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