Stored procedure parameters to R

Question

I am trying to run a simple R sum in R-Services using the parameters handed to a stored procedure but I do not know how to do it, nor find a good example. This is what I have so far:

IF OBJECT_ID ( 'TEST', 'P' ) IS NOT NULL   
    DROP PROCEDURE TEST;
GO

CREATE PROCEDURE TEST @a int = 0, @b int = 0 AS
BEGIN
    EXEC sp_execute_external_script
    @language = N'R'
    ,@script = N'print(sum(@a, @b))' -- how to pass params here?
    ,@input_data_1 = N'@a'
    return @a + @b;
END

EXEC dbo.TEST @a = 2, @b = 3
GO

My question is how to pass the variables read in the stored procedure (@a and @b) to the R script?

Answer 1

Consider using @params to pass SQL Server params to R variables as shown in this MS docs. Be sure to always return a dataframe object and leave @input_data_1 for SQL Server queries that you need passed into R script in the InputDataSet dataframe object (hence it is blank here).

CREATE PROCEDURE myProc (@a int, @b int)
AS
    EXEC sp_execute_external_script    
      @language = N'R'    
    , @script = N' OutputDataSet <- data.frame(SumRes = sum(a_r, b_r));'    
    , @input_data_1 = N'   ;' 
    , @params = N' @a_r int, @b_r int'  
    , @a_r = @a
    , @b_r = @b
    WITH RESULT SETS (([SumResult] int NOT NULL)); 

And then call procedure:

EXEC dbo.myProc @a = 2, @b = 3