-2

I have an SQL where clause: -

where table1.resource in @ListOfRes

I'm trying to add to the SQL command like this:-

command.Parameters.AddWithValue("@ListOfRes", "'1','2'");

The result I'm looking for is: -

where table1.resource in ('1','2')

But I can't seem to build a string this way, is there a different data type I need to be using?

Rufus L
  • 36,127
  • 5
  • 30
  • 43
Ian Thompson
  • 187
  • 1
  • 11
  • 1
    You can't pass lists this way. There are [alternatives](https://sommarskog.se/arrays-in-sql.html), among those manually splitting strings and table-valued parameters (and fully dynamic SQL, but that's a last resort). – Jeroen Mostert Aug 17 '20 at 14:38
  • 4
    Short answer - use Dapper. Longer answer - table valued parameter (https://stackoverflow.com/a/45407499/34092). – mjwills Aug 17 '20 at 14:39
  • This might be a bad answer but it seems you can do that directly in the command text instead of through a parameter. But if the list is exposed to the front end environment it leaves you exposed to injection. – KeithL Aug 17 '20 at 14:57
  • Related: https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause – Hans Kesting Aug 17 '20 at 14:58
  • SELECT * FROM Tags WHERE '|ruby|rails|scruffy|rubyonrails|' LIKE '%|' + Name + '|%' This is almost what I need, but I need to do it for int, is that possible? – Ian Thompson Aug 18 '20 at 12:38
  • SELECT * FROM RAWLOGS WHERE '|1|2|3|4|' LIKE '%|' + CONVERT(varchar(10), ResID) + '|%' - Works for me. – Ian Thompson Aug 18 '20 at 13:16

1 Answers1

1

Nope, Not working. This is not how SQL works, regardless what you do.

IN (@variable) takes the content of the variable as ONE ELEMENT. There is no way to put multiple elements in it. Live with it. You need one variable for every element, or another approach (temp file, then using a join etc.)..

This simply is not a supported approach.

TomTom
  • 61,059
  • 10
  • 88
  • 148