SQL Server stored procedure call with parameter in R

Question

Can someone give the code to call the SQL server stored procedure with a parameter in R?

This is all I have and it does not work:

dbhandle <- odbcDriverConnect('driver={SQL Server};server=SWD-CHISSIS01;database=GlobalRiskManagement;trusted_connection=true') data <-sqlQuery(dbhandle, "EXEC my_Stored_Proc @jyear=2013, @ddate=9/25/2013") odbcCloseAll() 

Thanks.

Answer 1

 dbhandle <- odbcDriverConnect('driver={SQL Server};server=SWD-CHISSIS01;database=GlobalRiskManagement;trusted_connection=true') data <-sqlQuery(dbhandle, "**set nocount on\n**EXEC my_Stored_Proc @jyear=2013, @ddate=9/25/2013") odbcCloseAll() 

Answer 2

Here's the answer:

 library(RODBC) GetData <- function (Field1) { conn<- odbcDriverConnect('driver={SQL Server};server=SERVER;database=DATABASE;trusted_connection=yes') data <- sqlQuery(conn,paste("exec my_STOREDPROC @Field1= '", Field1 , "';",sep =""),errors=FALSE) odbcCloseAll() data ) } 

Answer 3

I used the solution with the version of "SET NOCOUNT ON". But the stored procedure was completed and stopped abruptly. So I decided to use the shell using the sqlcmd command. Here I made a function:

 callStoredProc = function(server,BD,storedProc,sParams){ sFileName = paste("log_",gsub(":", "_", gsub("-", "_", gsub("\\.", "_", Sys.time()))),storedProc,".txt",sep="") sQuery = paste("sqlcmd -E -S ",server," -d ",BD," -Q \"EXECUTE ",storedProc," ",sParams,"\" -o \"",sFileName,"\"", sep="") shell(sQuery) }#callStoredProc 

Answer 4

To clarify, in the code above, ** should not be included in the code, so it should say: data <-sqlQuery (dbhandle, "set nocount on \ nEXEC my_Stored_Proc @jyear = 2013, @ddate = 9/25/2013")

This works for me. Thanks to all who responded.