How to build a parameterised query with IN sql keyword?

Question

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

The code is actually comparing personID to the entire string of "1,2,3,4,5", not against each item separated by commas. — Nick VanderPyle
– Nick VanderPyle, Commented Dec 27, 2011 at 15:53

Marc Gravell · Accepted Answer · 2011-12-27 15:59:52Z

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")

Nick VanderPyle · Accepted Answer · 2011-12-27 16:09:58Z

3

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) +
      ") ...";

edited Dec 27, 2011 at 16:09

answered Dec 27, 2011 at 16:04

Nick VanderPyle

2,9993 gold badges27 silver badges34 bronze badges

2 Comments

Nick VanderPyle Over a year ago

I just noticed Marc's answer. His suggestion is better. There are ORMs that make this problem a non-issue. SubSonic, EF, nHibernate, Dapper, etc.

Nime Cloud Over a year ago

I'll use this simpler version

Collectives™ on Stack Overflow

How to build a parameterised query with IN sql keyword?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related