Create SQL query with WHERE .. IN condition using list of integer

Question

I am not an expert of SQL and what I am trying to achieve is the following.

I have a list of integers called PersonIds. First of all I need to transform this list of integers to a list of strings. Why? Because as far as I have understood for the WHERE .. IN condition in SQL this is the kind of variable I need to feed to the query.

Then I need to modify the list of strings in order to prevent SQL injection and therefore inserting @ before every Id of the list.

At last I create the query and feed the list I just created.

What I tried to do is:

var listIds = string.Join(",@", PersonIds, 0, PersonIds.Count()));
var query = $"DELETE FROM PersonTable WHERE PersonId IN (@{listIds})";

There is something I am doing wrong. To recap I need to steps:

preparing a list (including mechanism to avoid SQL injection) that I need to feed to the query
create the query using as argument the list I created

Thanks in advance!

What is the likely value of PersonIds.Count? Is it ever likely to be > 2000? — mjwills
– mjwills, Commented Mar 20, 2018 at 23:42

Marc Gravell · Accepted Answer · 2018-03-20 23:09:56Z

7

Perhaps the simplest way is to add some Dapper:

List<int> listIds = ...
connection.Execute("DELETE FROM PersonTable WHERE PersonId IN @listIds",
        new { listIds });

Dapper does all the hard work of figuring out how to parameterize that, while still staying almost close to regular TSQL. You can also optionally enable string_split usage if you're using recent versions of SQL Server, to minimize the parameter count and query-plan cache size.

Note that the missing parentheses is deliberate and intentional - dapper treats this slightly differently to the regular IN (@foo, @bar) syntax.

answered Mar 20, 2018 at 23:09

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

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

Collectives™ on Stack Overflow

Create SQL query with WHERE .. IN condition using list of integer

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related