CODEWITHSUNDEEP
c#sql-server-ce
Nime Cloud
Nime Cloud

Reputation: 6397

How to build a parameterised query with IN sql keyword?

That's what I tried & failed: 

      string sql = "... WHERE [personID] IN (@sqlIn) ...";
      string sqlIn = "1,2,3,4,5";
      SqlCeCommand cmd.Parameters.Add("@sqlIn", SqlDbType.NText).Value = sqlIn;

      SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
      da.Fill(ds); // > Error

Error details:

The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses, except when these data types are used with the LIKE or IS NULL predicates.

Can't I pass all the IDs as one parameter? Should I add one by one all IDs?

P.S: Notice SqlCE

Upvotes: 2

Views: 273

Answers (2)

Nick VanderPyle
Nick VanderPyle

Reputation: 2999

You'll need to split the sqlIn string by comma, convert each to an integer, and build the IN statement manually.

  string sqlIn = "1,2,3,4,5";
  string inParams = sqlIn.Split(',');
  List<string> paramNames = new List<string>();
  for(var i = 0; i < inParams.Length; ++i){
        string paramName = "@param" + i.ToString();
        SqlCeCommand cmd.Parameters.Add(paramName, SqlDbType.Int).Value = int.Parse(inParams[i]);
        paramNames.Add(paramName);
  }

  string sql = "... WHERE [personID] IN (" +
      string.Join(",", paramNames) +
      ") ...";

Upvotes: 3

Marc Gravell
Marc Gravell

Reputation: 1062780

You can't parameterise that as a single parameter. Your query is doing an "in" on a single value, so is essentially:

... Where personId = '1,2,3,4,5'

(give or take a parameter). This is usually also an invalid or sub-optimal equality test, and certainly isn't what you were trying to query.

Options;

  • use raw concatenation: often involves a SQL injection risk, and allows poor query plan re-use
  • on full SQL server: use a UDF to split a single param
  • on full SQL server, use a TVP
  • add parameters dynamically, and add the various @param3 etc to the TSQL

The last is the most reliable, and "dapper-dot-net" has a feature built in to do this for you (since it is commonly needed):

int[] ids = ...
var rows = conn.Query<SomeType>(
    @"... Where Id in @ids",
    new { ids }).ToList();

This, when run via dapper-dot-net, will add a parameter per item in "ids", giving it the right value etc, and fixing the SQL so it executes, for example:

"... Where Id in (@ids0, @ids1, @ids2)"

(if there were 3 items in "ids")

Upvotes: 5

Related Questions