CODEWITHSUNDEEP
sqlrstored-proceduressp-executesqlrtvs
Nabi Shaikh
Nabi Shaikh

Reputation: 851

Passing More then One Parameter to [sp_execute_external_script] for Executing R code

Here is the scenario where i am trying to pass three parameter namely empid2 and Name1 & DOB1. i am unable to pass the parameter and get the resultant data frame at the OutPutDataset. Observed : with One Parameter passing i am getting the result but with more then one parameter i am unable to get the Output Data Frame.

ALTER procedure [dbo].[spGetAllEmployees] 
@empid2 int,
@Name1 Varchar(50)
--@DOB1 Date   
as    
Begin    
Declare @empid int
Declare @NewName varchar(50)

EXECUTE sp_execute_external_script
 @language = N'R'
,@script = N'df <- as.data.frame(InputDataSet);df[,1] <- df[,1]+1;OutputDataSet <-df'
,@input_data_1 = N'select * from tblEmployee where EmployeeId=@empid AND Name=@NewName'
,@params = N'@empid Int OUTPUT'
,@empid = @empid2
,@NewName =@Name1
--,@NewDOB=@DOB1
WITH RESULT SETS (([Col1] Int, [Col2] varchar(50), [Col3] varchar(50), Col4 varchar(50),Col5 varchar(50)));

End

-- EXEC dbo.spGetAllEmployees @empid2 ='1', @Name1='Nabi';

With Above Parameter empid2 and Name1 & DOB1 from given Data Frame/Data Table we are trying to subset the Table .
Sub setting the Table with 3 Parameter and process through RScript and data in OutputDataset

Upvotes: 2

Views: 1170

Answers (1)

Rupesh Kurkure
Rupesh Kurkure

Reputation: 68

You need to Write Output Syntax @Params in the query. 

ALTER procedure [dbo].[spGetAllEmployees] 
        @empid2 int,
        @Name1 Varchar(50),
        @DOB1 Date   
    as    
    Begin    
    Declare @empid int
    Declare @NewName varchar(50)
    Declare @NewDOB Varchar(50)

        EXECUTE sp_execute_external_script
         @language = N'R'
        ,@script = N'df <- as.data.frame(InputDataSet);df[,1] <- df[,1]+1;OutputDataSet <-df'
        ,@input_data_1 = N'select * from tblEmployee where EmployeeId=@empid AND Name=@NewName AND DOB=@NewDOB'
        ,@params = N'@empid Int OUTPUT,@NewName varchar(50) OUTPUT,@NewDOB Varchar(50) OUTPUT'
        ,@empid = @empid2
        ,@NewName =@Name1
        ,@NewDOB=@DOB1
        WITH RESULT SETS (([Col1] Int, [Col2] varchar(50), [Col3] varchar(50), Col4 varchar(50),Col5 varchar(50),Col6 varchar(50)));

    End

    -- EXEC dbo.spGetAllEmployees @empid2 ='1', @Name1='Nabi',@DOB1='2017-04-01';

Upvotes: 3

Related Questions