Our application uses SQL server 2016 and our Data Access layer is based on C#, .NET 4.7.2 framework.
There are some options to construct a SQL query with a IN-clause from code.
One is simple string replacement, which is not secure because of possible threats of SQL-Injection.
Second option is to use a SQLParameter of type string which contains comma separated values which are converted to a table on the DB server. But this cost expensive CPU usages on the DB server.
We use in some cases the custom CsvToInt Function.
The third option is to include in where clause OR statements instead of the IN clause. We will loop through list and create dynamically SQL parameters in the SQL statement.
But we think there will be multiple Query Execution plans created by the DB server. This because the amount of OR clauses/SQL parameters will vary per call.
Are there other options or data types which are available? Which efficient for code and more important efficient to the DB server.
Thank you in advance.
