Sql "IN" operator with using SqlParameter on varchar field

Question

I couldn't find how to use IN operator with SqlParameter on varchar column. Please check out the @Mailbox parameter below:

using (SqlCommand command = new SqlCommand())
{
    string sql =
    @"select    
         ei.ID as InteractionID,
         eo.Sentdate as MailRepliedDate
      from    
         bla bla
      where  
         Mailbox IN (@Mailbox)";
    command.CommandText = sql;
    command.Connection = conn;
    command.CommandType = CommandType.Text;                    
    command.Parameters.Add(new SqlParameter("@Mailbox", mailbox));                    
    SqlDataReader reader = command.ExecuteReader();
}

I tried these strings and query doesn't work.

string mailbox = "'abc@abc.com','def@def.com'"
string mailbox = "abc@abc.com,def@def.com"

I have also tried changed query Mailbox IN('@Mailbox')
and string mailbox = "abc@abc.com,def@def.com"

Any Suggestions? Thanks

Answer 1

That doesn't work this way.

You can parameterize each value in the list in an IN clause:

string sql =
  @"select    
         ei.ID as InteractionID,
         eo.Sentdate as MailRepliedDate
      from    
         bla bla
      where  
         Mailbox IN ({0})";
string mailbox = "abc@abc.com,def@def.com";
string[] mails = mailbox.Split(',');
string[] paramNames = mails.Select((s, i) => "@tag" + i.ToString()).ToArray();
string inClause = string.Join(",", paramNames);

using (var conn = new SqlConnection("ConnectionString"))
using (SqlCommand command = new SqlCommand(sql, conn))
{
    for (int i = 0; i < paramNames.Length; i++)
    {
        command.Parameters.AddWithValue(paramNames[i], mails[i]);
    }
    conn.Open();
    using (SqlDataReader reader = command.ExecuteReader())
    { 
        // ...
    }
}

Adapted from: https://stackoverflow.com/a/337792/284240