Search code examples
sqlrrodbc

Run SQL script from R with variables defined in R

I have an SQL script which I need to run using R Studio. However, my SQL script has one variable that is defined in my R environment. I am using dbGetQuery; however, I do not know (and I didn't find a solution) how to pass these variables.

library(readr)    
library(DBI)    
library(odbc)    
library(RODBC)

#create conection (fake one here)
con <- odbcConnect(...)

dt = Sys.Date()   

df = dbGetQuery(.con, statement = read_file('Query.sql'))

The file 'Query.sql' makes reference to dt. How do I make the file recognize my variable dt?

Solution

  • There are several options, but my preferred is "bound parameters".

    If, for instance, your 'Query.sql' looks something like

    select ...
from MyTable
where CreatedDateTime > ?

    The ? is a place-holder for a binding.

    Then you can do

    con <- dbConnect(...) # from DBI
df = dbGetQuery(con, statement = read_file('Query.sql'), params = list(dt))

    With more parameters, add more ?s and more objects to the list, as in

    qry <- "select ... where a > ? and b < ?"
newdat <- dbGetQuery(con, qry, params = list(var1, var2))

    If you need a SQL IN clause, it gets a little dicey, since it doesn't bind things precisely like we want.

    candidate_values <- c(2020, 1997, 1996, 1901)
qry <- paste("select ... where a > ? and b in (", paste(rep("?", length(candidate_values)), collapse=","), ")")
qry
# [1] "select ... where a > ? and b in ( ?,?,?,? )"
df <- dbGetQuery(con, qry, params = c(list(avar), as.list(candidate_values)))