Using multiple R variables in SQL chunk

Publishing Your Results
13

I had the same problem today and I think I found a solution using glue_sql() from the {glue} :package:.

To quote the glue_sql() help page:

If you place a * at the end of a glue expression the values will be collapsed with commas. This is useful for the SQL IN Operator for instance.

Now, this sounds useful indeed :wink:

grafik

And here is the rmarkdown code:

```{r}
library(DBI)
library(glue)
library(RSQLite)

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "mtcars", mtcars, overwrite = TRUE)

cyl_int <- c(4L, 6L)
cyl_sql <- glue_sql("{cyl_int*}")

```

```{sql, connection=con}
  SELECT * FROM mtcars
  WHERE cyl IN (?cyl_sql)
  LIMIT 3
```
14 Likes