title: R RODBCext and Parameterizing IN statement?
body: <p>I've been working to parameterize a SQL Statement that uses the IN statement in the WHERE clause.  I'm using rodbcext library for parameterizing but it seems to lack expansion of a list.</p>
  
  <p>I was hoping to write code such as</p>
  
  <pre><code>sqlExecute("SELECT * FROM table WHERE name IN (?)", c("paul","ringo","john", "george")
  </code></pre>
  
  <p>I'm using the following code but wondered if there's an easier way.</p>
  
  <pre><code>library(RODBC)
  library(RODBCext)
  
  # Search inputs
  names &lt;- c("paul", "ringo", "john", "george")
  
  # Build SQL statement
  qmarks &lt;- replicate(length(names), "?")
  stringmarks &lt;- paste(qmarks, collapse = ",")
  sql &lt;- paste("SELECT * FROM tableA WHERE name IN (", stringmarks, ")")
  # expand to Columns - seems to be the magic step required
  bindnames &lt;- rbind(names)
  
  # Execute SQL statement
  dbhandle &lt;- RODBC::odbcDriverConnect(connectionString)
  result &lt;- RODBCext::sqlExecute(dbhandle, sql, bindnames, fetch = TRUE)
  RODBC::odbcClose(dbhandle)
  </code></pre>
  
  <p>It works but feel I'm using R to expand the strings in the wrong way (bit new to R - so many ways to do the same thing wrong).  Somebody will probably say "that creates factors - never do that" :-)</p>
  
  <p>I found this article which suggest I'm on the right track but it doesn't discuss having to expand the "?" and turn the list into columns of a data.frame  </p>
  
  <p><a href="https://stackoverflow.com/questions/8639221/r-rodbc-putting-list-of-numbers-into-an-in-statement">R RODBC putting list of numbers into an IN() statement</a></p>
  
  <p>Thank you.</p>
  
  <p>UPDATE:   As Benjamin shows below - the sqlExecute function can handle a list() of inputs.  However upon inspection of the resulting SQL I discovered that it uses cursors to rollup the results.  This significantly increases the CPU and I/O over the sample code I show above.</p>
  
  <p>While the library can indeed solve this for you, for large results it may be too expensive.  There are two answers and it depends upon your needs.</p>