How to pass function parameters in sql query in R?

Question

I have the following function in R:

dbhandle <- odbcDriverConnect('driver={SQL Server};
                              server=myServer;database=myDB;
                              trusted_connection=true')
func <- function(x){
     sqlQuery(dbhandle, 'select attr from myTable where cond = x')
}

I want to know how to pass the x parameter to the where condition for the cond attribute. For example, I want to calculate func(10), then the query must be:

select attr from myTable where cond = 10

Please properly define what you mean by *parameter* as there is a concept called SQL parameterization which the below majority of answers do not employ except for one for the RODBC package. — Parfait, Oct 30 '17 at 17:04

score 4 · Answer 1 · answered Oct 30 '17 at 16:22

when using RODBC, I prefer to use parameterized queries. This becomes more valuable when you are passing character strings, as this avoids SQL Injection.

library(RODBCext)

sqlExecute(dbhandle, 
           "select attr from myTable where cond = ?",
           data = list(cond = 10),
           fetch = TRUE,
           stringsAsFactors = FALSE)

score 3 · Accepted Answer · answered Oct 30 '17 at 15:56

3

You could try with paste:

func <- function(x){
  sqlQuery(dbhandle, paste('select attr from myTable where cond =', x))
}

answered Oct 30 '17 at 15:56

LyzandeR

37,047
12
77
87

2

Warning: This does not take sql injection into account https://xkcd.com/327/ – rapto Apr 14 '20 at 12:02

score 1 · Answer 3 · answered Oct 30 '17 at 15:59

1

I like the glue package for these things, though it's really the same as paste, just prettier :

library(glue)

func <- function(x){
     sqlQuery(dbhandle, glue('select attr from myTable where cond = {x}'))
}

answered Oct 30 '17 at 15:59

moodymudskipper

46,417
11
121
167

score -2 · Answer 4 · answered Oct 30 '17 at 16:30

-2

In this simple case you could even use dplyr itself:

library(dplyr)

func <- function(x) {
     tbl(dbhandle, "myTable") %>%
       select(attr) %>%
       filter(cond == x) %>%
       collect()
}

answered Oct 30 '17 at 16:30

amarchin

2,044
1
16
32

How to pass function parameters in sql query in R?

4 Answers4