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?

What does your current `EXEC` call return? An error? No results? — Parfait, Jun 12 '17 at 16:02
The message is: Msg 214, Level 16, State 179, Procedure sp_execute_external_script, Line 1 Procedure expects parameter '@input_data_1' of type 'nvarchar(max)'. — Leonardo Lanchas, Jun 12 '17 at 19:27

score 2 · Accepted Answer · edited Jun 13 '17 at 10:20

2

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

edited Jun 13 '17 at 10:20

Hong Ooi

56,353
13
134
187

answered Jun 13 '17 at 00:51

Parfait

104,375
17
94
125

thanks for the answer. It works, but I had to remove the @ in the R script to make it (SumRes = sum(a_r, b_r)) Please edit the answer when it best suits you. – Leonardo Lanchas Jun 13 '17 at 07:02
Indeed...sorry for that small overlook! Thanks @HongOoi for the edit. Glad to help. – Parfait Jun 13 '17 at 14:03

score 0 · Answer 2 · answered Jun 12 '17 at 15:45

0

run below query

EXEC dbo.TEST 2,  3

answered Jun 12 '17 at 15:45

NIts577

420
3
13

How is it different from EXEC dbo.TEST @a = 2, @b = 3 – Leonardo Lanchas Jun 12 '17 at 15:48

score 0 · Answer 3 · edited Nov 01 '17 at 10:17

0

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... :)

edited Nov 01 '17 at 10:17

DiskJunky

4,750
3
37
66

answered Nov 01 '17 at 09:57

user8867335

1

Stored procedure parameters to R

3 Answers3