How can I supply a List to a SQL parameter?

Question

I have a SQL statement like the following:

...
const string sql = @"UPDATE PLATYPUS
SET DUCKBILLID = :NEWDUCKBILLID
WHERE PLATYPUSID IN (:ListOfInts)";
...
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.

@B.ClayShannon: hi, yes, I simply noticed that several similar questions are basically the same linked question so I went through all of them and added the link to the oldest one. I don't have anything with either of these, I just wanted to do some cleanup as I usually do when I find a common thread. — Groo, Sep 08 '16 at 07:24

score 12 · Answer 1 · answered Jul 16 '12 at 16:14

12

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.

answered Jul 16 '12 at 16:14

Felice Pollano

31,141
8
67
108

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](http://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx) 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 03 '14 at 20:26

score 5 · Answer 2 · answered Jul 16 '12 at 16:13

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))
AS   
BEGIN
    DECLARE @List TABLE
    (
        Value varchar(30)
    )

    DECLARE
        @Value varchar(30),
        @Pos int

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

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

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

            SET @CSV = RIGHT(@CSV, LEN(@CSV) - @Pos)
            SET @Pos = CHARINDEX(',', @CSV, 1)
        END
    END     

    INSERT @Result
    SELECT
        Value
    FROM
        @List

    RETURN
END

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

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

SELECT 
    ProductID, 
    ProductName, 
    UnitPrice
FROM 
    Products
WHERE
    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.

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

score 4 · Answer 3 · answered Jul 16 '12 at 20:18

4

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.

answered Jul 16 '12 at 20:18

James Simpson

43
3

Joel Coehoorn · Answer 4 · 2012-07-16T16:52:56.370

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.

Shyju · Answer 5 · 2012-07-16T16:34:14.057

0

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

List<int> ints = new List<int>();
ints.Add(4);
ints.Add(6);
ints.Add(2);

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

ocmd.Parameters.Add("ListOfInts",concatenatedIds);

edited Jul 16 '12 at 16:34

answered Jul 16 '12 at 16:13

Shyju

197,032
96
389
477

10

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 03 '14 at 20:27
It won't work this way – leox May 14 '20 at 16:12

How can I supply a List to a SQL parameter?

5 Answers5

Linked

Related