CODEWITHSUNDEEP
sql-serverrmicrosoft-r
Leonardo Lanchas
Leonardo Lanchas

Reputation: 1668

Stored procedure parameters to R

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?

Upvotes: 4

Views: 2419

Answers (3)

user8867335
user8867335

Reputation: 1

Field1 <- 'fd'
Field2 <- '20'
query <- paste0(
  "exec df_test @Name = ", Field1, ", @ProductNumber = ", Field2, ""
)
sqlQuery(cn, query)
odbcClose(cn)

Execute your stored procedure like this for r code

Happy Coding... :)

Upvotes: 0

Parfait
Parfait

Reputation: 107567

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

Upvotes: 2

NIts577
NIts577

Reputation: 420

run below query

EXEC dbo.TEST 2,  3

Upvotes: 0

Related Questions