How to return a variable when Running R from SQL Server

Question

I am trying to run a basic R script from SQL Server through a stored procedure. I am returning a dataset and also want to return/output a variable, but am not able to do that.

Below is the stored procedure I have

DROP PROC IF EXISTS get_iris_dataset;  
go  
CREATE PROC get_iris_dataset  
AS  
BEGIN  
declare @OutName float
 EXEC   sp_execute_external_script  
   @language = N'R'  
 , @script = N'
    iris_data <- iris
    out = 5.1;'  
 , @input_data_1 = N''  
 , @output_data_1_name = N'iris_data'  
 ,@params= N'@out float OUTPUT'
 ,@out = @OutName OUTPUT

 WITH RESULT SETS (("Sepal.Length" float not null,   
       "Sepal.Width" float not null,  
    "Petal.Length" float not null,   
    "Petal.Width" float not null, "Species" varchar(100)));  
END;  
return 0 
go  

I am calling the procedure as below:

declare @OutputVar float
exec @OutputVar = get_iris_dataset
print @OutputVar

It does output the dataset, but I am not able to store the value of the OutputVar.

I am not able to return the value of OutputVar even when I do not output the dataset, and only try to capture the value of OutputVar. Any help is appreciated.

Thanks in advance!

Answer 1

What I'd do is use the StoredProcedure function (part of the sqlrutils package, which is in turn part of MS R Server 9.0.1) to wrap the R code into a stored procedure. This is slightly different to your original code, since the return values are wrapped in a list. But the idea is still the same.

library(sqlrutils)

testfunc <- function()
{
    iris_dataset <- iris
    out <- 5.1
    list(iris_dataset=iris_dataset, out=out)
}

sp <- StoredProcedure(testfunc, "spTestFunc",
    OutputData("iris_dataset"),
    OutputParameter("out", "numeric"),
    connectionString="<your_connection_string>")

registerStoredProcedure(sp)

Having done that, you can right-click on the proc in SSMS, select "execute Stored Procedure", and SSMS will generate some code for you. The code will look like this:

USE [databasename]
GO

DECLARE @return_value int,
        @out_outer float

EXEC    @return_value = [dbo].[spTestFunc]
        @out_outer = @out_outer OUTPUT

SELECT  @out_outer as N'@out_outer'

SELECT  'Return Value' = @return_value

GO

And if you only want the output number, you can simplify the code down to this:

DECLARE @out_outer float

EXEC    [dbo].[spTestFunc]
        @out_outer = @out_outer OUTPUT

SELECT  'Return Value' = @out_outer
GO