Building an SQL in list using C# variable

Question

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?

You can't pass lists this way. There are alternatives, among those manually splitting strings and table-valued parameters (and fully dynamic SQL, but that's a last resort). — Jeroen Mostert
– Jeroen Mostert, Commented Aug 17, 2020 at 14:38
Short answer - use Dapper. Longer answer - table valued parameter (stackoverflow.com/a/45407499/34092). — mjwills
– mjwills, Commented Aug 17, 2020 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
– KeithL, Commented Aug 17, 2020 at 14:57
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
– Ian Thompson, Commented Aug 18, 2020 at 12:38

TomTom · Accepted Answer · 2020-08-17 14:38:59Z

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.

answered Aug 17, 2020 at 14:38

TomTom

62.4k11 gold badges95 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ignotus · Accepted Answer · 2023-12-12 09:40:56Z

0

Since SQL Server 2008, there is a solution, table valued parameters. See C# with SQL Server SELECT WHERE IN with data list

answered Dec 12, 2023 at 9:40

ignotus

6586 silver badges9 bronze badges

Collectives™ on Stack Overflow

Building an SQL in list using C# variable

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related