Retrieve output from stored procedure result in R

Question

I'm trying to call a stored procedure and retrieve the output. I'm calling SQL Server using the DBI-compliant odbc package. How can I use this package? The following code returns the number of rows affected.

sql     <- "EXEC schema.prc_person @name_first='bob'"
channel <- DBI::dbConnect(odbc::odbc(), dsn="db-remote")
guy     <- DBI::dbExecute( channel, sql)
DBI::dbDisconnect(channel); rm(channel)

The stored procedure is basically a SELECT query, but encapsulates some calculations, which is why this isn't a straight-forward query.

SELECT value 
FROM schema.tbl_person
WHERE name_first=@name_first --plus some other stuff in the WHERE clause

For reference, these two statements do what I need (using the RODBC and RODBCext package). Also in the odbc package, I've tried dbCallProc(), dbSendQuery(), and dbSendStatement().

channel <- RODBC::odbcConnect(dsn="db-remote")
guy     <- RODBCext::sqlExecute(channel, "EXEC schema.tbl_person @name_first='bob'", fetch=T) # Notice the 'fetch' parameter
RODBC::odbcClose(channel); rm(channel)

channel <- RODBC::odbcConnect(dsn="db-remote")
guy     <- RODBC::sqlQuery(channel, "EXEC schema.tbl_person @name_first='bob'")
RODBC::odbcClose(channel); rm(channel)

Answer 1

You can use dbGetQuery:

sql     <- "EXEC schema.prc_person @name_first='bob'"
channel <- DBI::dbConnect(odbc::odbc(), dsn="db-remote")
guy     <- DBI::dbGetQuery(channel, sql)
DBI::dbDisconnect(channel); rm(channel)