Take the 2-minute tour ×

I have a SQL statement like the following:

const string sql = @"UPDATE PLATYPUS
ocmd.Parameters.Add("ListOfInts", ??WhatNow??);

How can I provide the comma separated list of ints, which could be any (reasonable*) number of values

  • By "reasonable" in this case I mean between one and a couple dozen.
share|improve this question
why not stored the values in a string separated by comma ? –  Waqar Janjua Jul 16 '12 at 16:16
add comment

6 Answers

up vote 6 down vote accepted

Try something like this:

ocmd.Parameters.Add("ListOfInts", String.Join(",",myList.ToArray());

The String.Join method takes all elements in an array and places the specified character between them.

share|improve this answer
Why was this accepted as the answer? You can't really do it this way. –  Lasse V. Karlsen Jan 3 at 20:20
His question: How can I provide the comma separated list of ints... How does this not answer his question? –  Abe Miessler Jan 3 at 21:23
Because the correct answer is: It can't be done this way! You can't provide a single parameter that will work in an IN clause with a string parameter. A table-valued parameter might work, a string parameter will not work. Now, I understand that you answered the question that was asked, but my point is that you need to answer the question that was implied: how to make this work. Answer: It can't. –  Lasse V. Karlsen Jan 3 at 23:12
Alright, fair enough. –  Abe Miessler Jan 3 at 23:17
add comment

You can't, you have to create some helper function that replaces :ListOfInts with ( for example ) :I0,:I1,:I2... and pass the parameter by adding one by one in code. The function you want is gracefully emulated by Dapper.Net in its list support.

share|improve this answer
This answer is correct, as opposed to the accepted one and other 3-upvoted one. You can of course use a bunch of parameters, and inject those, basically getting an SQL like where id in (@id1, @id2, @id3, ...). Also there are Table-valued parameters now, but I don't know if/how they can be leveraged to actually use 1 parameter to specify the list of "IN"-values to match against. –  Lasse V. Karlsen Jan 3 at 20:26
add comment

Use the Join method of string on the Array of Ints. You can mention comma as the seperator

List<int> ints = new List<int>();

string concatenatedIds= string.Join(",", ints.ToArray());

The output (value in concatenatedIds) will be 4,6,2. You can use that string as the parameter value for your IN clause

share|improve this answer
While this will add a parameter just fine, it will not work with an in (@ListOfInts) clause in the SQL. Parameters doesn't work that way. Wishful thinking, I know, but no. –  Lasse V. Karlsen Jan 3 at 20:27
add comment

I think the only possible solution is to pass comma-separated values, which you can convert to a table in SQL using a function. Here is the function I am using

CREATE FUNCTION dbo.CSVToList (@CSV varchar(3000)) 
    RETURNS @Result TABLE (Value varchar(30))
        Value varchar(30)

        @Value varchar(30),
        @Pos int

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

    IF REPLACE(@CSV, ',', '') <> ''
        WHILE @Pos > 0
            SET @Value = LTRIM(RTRIM(LEFT(@CSV, @Pos - 1)))

            IF @Value <> ''
                INSERT INTO @List (Value) VALUES (@Value) 

            SET @CSV = RIGHT(@CSV, LEN(@CSV) - @Pos)

    INSERT @Result


and you can use the following code (for example) to perform operations:

DECLARE @CSV varchar(100)
SET @CSV = '30,32,34,36,40'

    ProductID IN (SELECT * FROM dbo.CSVToLIst(@CSV))

I took the code from here: http://www.geekzilla.co.uk/view5C09B52C-4600-4B66-9DD7-DCE840D64CBD.htm

Hope it helps.

share|improve this answer
I forgot to say that you have to use the String.join method to aggregate all values and then pass them as parameters to the SQL code. –  Umar Jamil Jul 16 '12 at 16:14
add comment

The best way to handle this is to keep the list in the database, such that you could write a select query to return the values in your list. The question is, how can you do this when the data originates at the client? Most of the time, this data is hand-picked by the user, one record at a time. In that case, you can use a table (think: "shopping cart") that adds records for the values one at a time, as the user selects them. If it's more of a batch job, you may want to use a BulkInsert process to create the records.

share|improve this answer
add comment

You don't state which database you are using, but if it is SQL Server 2008+ you may also want to consider Table Valued Parameters.

For your example the added complexity may not be worthwhile, but they can be useful in other scenarios.

share|improve this answer
add comment

Your Answer


