I want to execute a stored procedure on a (Microsoft) SQL Server (2012) that has an OUTPUT parameter and want to read the result set as well as the output value(s) back into R.
Is there a way to
- get the OUTPUT parameter value
- and at the same time (without executing the stored procedure a second time) also
- get the result set of the stored procedure?
Example for my current work-around (using RODBCext instead of RODBC to prevent SQL code injection):
-- example stored procedure
CREATE PROCEDURE output_test
@ID INT,
@result INT OUTPUT
AS
BEGIN
SET NOCOUNT ON
SELECT @result = 42 -- OUTPUT parameter
-- FROM whateveryouwant
-- WHERE ID = @ID
SELECT * FROM anothertable WHERE ID = @ID -- result set
END
Work-around in R using RODBCext (would also work with RODBC if I'd paste the ID into the sql query string):
library(RODBCext)
con <- odbcConnect("TEST_DATABASE")
# first query to get the OUTPUT parameter
sql <- "DECLARE @result_output INT;
EXECUTE output_test @ID = ?, @result = @result_output OUTPUT;
SELECT @result_output AS result"
result <- RODBCext::sqlExecute(con, query = sql, data = list(ID = 100), fetch = TRUE)
# second query to get the result set
sql2 <- "EXECUTE output_test @ID = ?, @result = @result_output OUTPUT;"
resultset <- RODBCext::sqlExecute(con, query = sql2, data = list(ID = 100), fetch = TRUE)
odbcClose(con)
PS: There are related questions here that do not offer a full solution to my question, e. g.:
