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();
}
10
  • 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, newer versions of sql-server can use json as well, so those are some options. Commented Mar 10, 2023 at 14:45
  • You can use table values parameter and use IN clause. Commented Mar 10, 2023 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. Commented Mar 10, 2023 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. learn.microsoft.com/en-us/dotnet/csharp/language-reference/… Commented Mar 10, 2023 at 14:54
  • @Chetan If I use IN together with parameters, then there will be no restrictions on creating parameters in the loop? Commented Mar 10, 2023 at 14:57

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.