Sql "IN" operator with using SqlParameter on varchar field

Since you are using MS SQL server, you have 4 choices, depending on the version. Listed in order of preference.

1. Pass a composite value, and call a custom a CLR or Table Valued Function to break it into a set. see here.

You need to write the custom function and call it in the query. You also need to load that Assembly into you database to make the CLR accessible as TSQL.

If you read through all of Sommarskog's work linked above, and I suggest you do, you see that if performance and concurrency are really important, you'll probably want to implment a CLR function to do this task. For details of one possible implementation, see below.

2. Use a table valued parameter. see here.

You'll need a recent version of MSSQL server.

3. Pass mutiple parameters.

You'll have to dynamically generate the right number of parameters in the statement. Tim Schmelter's answer shows a way to do this.

4. Generate dynamic SQL on the client. (I don't suggest you actually do this.)

You have to careful to avoid injection attacks and there is less chance to benefit from query plan resuse.

dont do it like this.

One possible CLR implementation.

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class CLR_adam
{
    [Microsoft.SqlServer.Server.SqlFunction(
       FillRowMethodName = "FillRow_char")
    ]
    public static IEnumerator CLR_charlist_adam(
      [SqlFacet(MaxSize = -1)]
      SqlChars Input,
      [SqlFacet(MaxSize = 255)]
      SqlChars Delimiter
       )
    {
        return (
            (Input.IsNull || Delimiter.IsNull) ?
            new SplitStringMulti(new char[0], new char[0]) :
            new SplitStringMulti(Input.Value, Delimiter.Value));
    }

    public static void FillRow_char(object obj, out SqlString item)
    {
        item = new SqlString((string)obj);
    }

    [Microsoft.SqlServer.Server.SqlFunction(
       FillRowMethodName = "FillRow_int")
    ]
    public static IEnumerator CLR_intlist_adam(
      [SqlFacet(MaxSize = -1)]
      SqlChars Input,
      [SqlFacet(MaxSize = 255)]
      SqlChars Delimiter
       )
    {
        return (
            (Input.IsNull || Delimiter.IsNull) ?
            new SplitStringMulti(new char[0], new char[0]) :
            new SplitStringMulti(Input.Value, Delimiter.Value));
    }

    public static void FillRow_int(object obj, out int item)
    {
        item = System.Convert.ToInt32((string) obj);
    }


    public class SplitStringMulti : IEnumerator
    {
        public SplitStringMulti(char[] TheString, char[] Delimiter)
        {
            theString = TheString;
            stringLen = TheString.Length;
            delimiter = Delimiter;
            delimiterLen = (byte)(Delimiter.Length);
            isSingleCharDelim = (delimiterLen == 1);

            lastPos = 0;
            nextPos = delimiterLen * -1;
        }

        #region IEnumerator Members

        public object Current
        {
            get
            {
                return new string(
                    theString,
                    lastPos,
                    nextPos - lastPos).Trim();
            }
        }

        public bool MoveNext()
        {
            if (nextPos >= stringLen)
                return false;
            else
            {
                lastPos = nextPos + delimiterLen;

                for (int i = lastPos; i < stringLen; i++)
                {
                    bool matches = true;

                    //Optimize for single-character delimiters
                    if (isSingleCharDelim)
                    {
                        if (theString[i] != delimiter[0])
                            matches = false;
                    }
                    else
                    {
                        for (byte j = 0; j < delimiterLen; j++)
                        {
                            if (((i + j) >= stringLen) || 
                                (theString[i + j] != delimiter[j]))
                            {
                                matches = false;
                                break;
                            }
                        }
                    }

                    if (matches)
                    {
                        nextPos = i;

                        //Deal with consecutive delimiters
                        if ((nextPos - lastPos) > 0)
                            return true;
                        else
                        {
                            i += (delimiterLen-1);
                            lastPos += delimiterLen;
                        }
                    }
                }

                lastPos = nextPos + delimiterLen;
                nextPos = stringLen;

                if ((nextPos - lastPos) > 0)
                    return true;
                else
                    return false;
            }
        }

        public void Reset()
        {
            lastPos = 0;
            nextPos = delimiterLen * -1;
        }

        #endregion

        private int lastPos;
        private int nextPos;

        private readonly char[] theString;
        private readonly char[] delimiter;
        private readonly int stringLen;
        private readonly byte delimiterLen;
        private readonly bool isSingleCharDelim;
    }
};

Why not just concactenate your mailbox variable with your sql string variable