ExecuteInterpolatedSql with a WHERE IN Int[] statement

Question

I'm trying to execute a query using ExecuteInterpolatedSqlAsync that checks if a specific substring is included in an array of integers.

What I've tried is this:

var value = "example";
var integers = new List<int> { 100, 404, 777 };
FormattableString query = 
  $"DELETE FROM Table1
  WHERE Type = {value}
  AND CAST(SUBSTRING(Value, 5, 7) AS INTEGER) IN ({string.Join(",", integers)})";

await _signInDbContext.ExecuteSqlInterpolatedAsync(query);

When I debug this, it shows the string as follows:

DELETE FROM Table1 WHERE Type = example AND CAST(SUBSTRING(Value, 5, 7) AS INTEGER) IN (100,404,700)

But when it runs the query against the Database it puts quotes around both parameters:

DELETE FROM Table1 WHERE Type = 'example' AND CAST(SUBSTRING(Value, 5, 7) AS INTEGER) IN ('100,404,700')

This makes sense because both are a string. But it's not what I want (obviously), I get this error:

Conversion failed when converting the nvarchar value '100,404,777' to data type int

How can I include an integer array in this ExecuteSqlInterpolatedAsync query?

Yong Shun · Answer 1 · 2022-08-01T08:33:48.203

1

Believe that in SQL, you need STRING_SPLIT the parsed value with separator: ,, then cast the value to INTEGER

string integerStr = string.Join(",", integers);

FormattableString query = 
  $"DELETE FROM Table1
  WHERE Type = {value}
  AND CAST(SUBSTRING(Value, 5, 7) AS INTEGER) 
    IN (SELECT CAST(value AS INTEGER) FROM STRING_SPLIT({integerStr}, ','))";

SQL

DELETE FROM Table1
  WHERE Type = @value
  AND CAST(SUBSTRING(Value, 5, 7) AS INTEGER) 
    IN (SELECT CAST(value AS INTEGER) FROM STRING_SPLIT('100,404,777', ','))

Updated: Miss out on the explanation of why string concatenation doesn't work correctly.

According to Passing parameters,

While this syntax may look like String.Format syntax, the supplied value is wrapped in a DbParameter and the generated parameter name inserted where the {0} placeholder was specified.

The value was passed as the DbParameter to the query as

DELETE FROM Table1
  WHERE Type = @value
  AND CAST(SUBSTRING(Value, 5, 7) AS INTEGER) IN (@P1)

So the final query will be looked like:

DELETE FROM Table1
  WHERE Type = @value
  AND CAST(SUBSTRING(Value, 5, 7) AS INTEGER) IN ('100,404,700')

edited Aug 01 '22 at 08:33

answered Aug 01 '22 at 07:48

Yong Shun

35,286
4
24
46

It's telling me `Invalid object name 'STRING_SPLIT'`, am I missing something? It's executing this: `... IN (SELECT CAST(value AS INTEGER) FROM STRING_SPLIT(@p1, ',')` – Jesse Aug 01 '22 at 08:08
May I know which SQL Server version you used? – Yong Shun Aug 01 '22 at 08:09
It's `12.0.2000.8`, so I'm guessing it's too old ;-) – Jesse Aug 01 '22 at 08:12
I've just been corrected by my colleague that we use an Azure SQL Database which should be up to date – Jesse Aug 01 '22 at 08:15
Hmmm, sorry not SQL Server version, I suspect that it is due to the compatibility level of your database instance, run the **SELECT** command according to this [answer](https://stackoverflow.com/a/47206082/8017690). The STRING_SPLIT need for Compatibility level 130. – Yong Shun Aug 01 '22 at 08:22
1

Either may discuss updating the DB instance compatibility level or you may need to write a table-value function such as [Split function equivalent in T-SQL?](https://stackoverflow.com/a/697543/8017690) – Yong Shun Aug 01 '22 at 08:27

score 0 · Answer 2 · answered Aug 01 '22 at 10:48

The real solution here is to use a Table Valued Parameter.

First, create a Table Type in SSMS. I usually keep a few standard one and two column types for this purpose.

CREATE TYPE dbo.IntList (value int PRIMARY KEY);

Then use it like this

var value = "example";
var integers = new List<int> { 100, 404, 777 };

var table = new DataTable { Columns = {
    {"value", typeof(int)},
} };

foreach (var v in integers)
    table.Rows.Add(v);

var tableParam = new SqlParameter("@tbl", SqlDbType.Structured)
{
    TypeName = "dbo.IntList",
    Value = table,
};

FormattableString query = $@"
DELETE FROM Table1
  WHERE Type = {value}
  AND CAST(SUBSTRING(Value, 5, 7) AS INTEGER) IN
      (SELECT value FROM {tableParam})";

await _signInDbContext.ExecuteSqlInterpolatedAsync(query);

// alternately use Raw

const string query = @"
DELETE FROM Table1
  WHERE Type = {0}
  AND CAST(SUBSTRING(Value, 5, 7) AS INTEGER) IN
      (SELECT value FROM {1})";

await _signInDbContext.ExecuteSqlRawAsync(query, value, tableParam);

ExecuteInterpolatedSql with a WHERE IN Int[] statement

2 Answers2