1

What is the best way to process a list of phone numbers in the where condition? Use a simple loop? Or is it possible to modify the Sql query for MS SQL Server somehow?

string sqlExpression = @'select d.* from MobilePhone m
    join User u on m.User_IDREF = u.User_ID
    join Device d on u.User_ID = d.User_ID
    WHERE m.MonilePhone = @MobilePhone;' // I want to insert a list '+7897654321' AND '+7878765444' AND so on...

SqlConnection connection = new SqlConnection(connectionString);
try
{
    await connection.OpenAsync();

    SQLCommand command = new SqlCommand(sqlExpression, connection);

    command.Parameters.Add("@MobilePhone", SqlDbType.VarChar);
    command.Parameters["@MobilePhone"].Value = ''; // I want to insert a list '+7897654321' AND '+7878765444' AND so on... Maybe to use a loop for this?
    
    SqlDataReader reader = await command.ExecuteReaderAsync();
}
stuartd
  • 70,509
  • 14
  • 132
  • 163
Timur
  • 45
  • 4
  • You could pass an XML parameter containing your "list", or use a table value parameter: [how-to-pass-table-value-parameters-to-stored-procedure-from-net-code](https://stackoverflow.com/questions/5595353/how-to-pass-table-value-parameters-to-stored-procedure-from-net-code), newer versions of sql-server can use json as well, so those are some options. – Ryan Wilson Mar 10 '23 at 14:45
  • You can use table values parameter and use `IN` clause. – Chetan Mar 10 '23 at 14:47
  • 2
    In addition to passing a list of values as XML or a TVP, you can use a comma-separated list and parse with `STRING_SPLIT` or as a JSON array and parse with `OPENSJON`, depending on your SQL Server version. – Dan Guzman Mar 10 '23 at 14:52
  • Slight detour....I would highly recommend you wrap your connection and command objects (and anything else using the iDisposable interface) in a USING statement. https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/using-statement – Sean Lange Mar 10 '23 at 14:54
  • @Chetan If I use IN together with parameters, then there will be no restrictions on creating parameters in the loop? – Timur Mar 10 '23 at 14:57
  • @Timur If you follow the suggestions given, you won't need a loop. SQL uses a set based approach, and loops should generally be unnecessary. – Ryan Wilson Mar 10 '23 at 15:00
  • @Ryan Wilson Thanks. There is a question... Does your example fit the WHERE condition? It looks like it only works for INSERT – Timur Mar 10 '23 at 15:04
  • @Timur you would join your parameter, be it xml, json or table value, on the appropriate table in your query and your where clause wouldn't be necessary. Here's some links to help: [join-on-a-table-valued-parameter-sql-server-t-sql](https://stackoverflow.com/questions/34819807/join-on-a-table-valued-parameter-sql-server-t-sql), [join-on-data-from-xml-in-t-sql](https://stackoverflow.com/questions/10498300/join-on-data-from-xml-in-t-sql), and... – Ryan Wilson Mar 10 '23 at 15:06
  • [how-to-join-json-list-with-a-table-in-sql-server-using-openjson](https://stackoverflow.com/questions/41424402/how-to-join-json-list-with-a-table-in-sql-server-using-openjson) – Ryan Wilson Mar 10 '23 at 15:08
  • You can also derive a table from a list of strings in SQL like this: `(values ('+7897654321'), ('+7897654321')) PhoneList(InputPhone)`. So your sql would be: `select d.* from MobilePhone m join User u on m.User_IDREF = u.User_ID join Device d on u.User_ID = d.User_ID join (values ('+7897654321'), ('+7897654321')) PhoneList(InputPhone) on PhoneList.InputPhone = m.MobilePhone` – thewallrus Mar 10 '23 at 15:41

0 Answers0