Search code examples
sqlrrodbc

Pass R variable to RODBC's sqlQuery?

Is there any way to pass a variable defined within R to the sqlQuery function within the RODBC package?

Specifically, I need to pass such a variable to either a scalar/table-valued function, a stored procedure, and/or perhaps the WHERE clause of a SELECT statement.

For example, let:

x <- 1 ## user-defined

Then,

example <- sqlQuery(myDB,"SELECT * FROM dbo.my_table_fn (x)")

Or...

example2 <- sqlQuery(myDB,"SELECT * FROM dbo.some_random_table AS foo WHERE foo.ID = x")

Or...

example3 <- sqlQuery(myDB,"EXEC dbo.my_stored_proc (x)")

Obviously, none of these work, but I'm thinking that there's something that enables this sort of functionality.

Solution

  • Build the string you intend to pass. So instead of

    example <- sqlQuery(myDB,"SELECT * FROM dbo.my_table_fn (x)")

    do

    example <- sqlQuery(myDB, paste("SELECT * FROM dbo.my_table_fn (", 
                                x, ")", sep=""))

    which will fill in the value of x.