none
R: How to retrieve the result of R script to a variable inside a stored procedure? RRS feed

  • Question

  • Hi All,

    We have written R script inside sql server to produce some result. We need to use this value to do some other calculation. All these steps must be inside a stored procedure as we need to pass this SP to another application. How can we assign the Outputdataset value to a variable?

    Please suggest.

    Thanks,

    Julie

    Wednesday, February 15, 2017 2:45 PM

All replies

  • Julie,

    I'm not sure if my answer here is exactly answering what you're trying to do. If the answer below isn't enough detail can you provide an example?

    -- Create a #temp table that matches the structure of the output data set
    IF object_id('tempdb..#t1') IS NOT NULL
    	DROP TABLE #t1
    
    CREATE TABLE #t1 (
    	SepalLength FLOAT
    	, SepalWidth FLOAT
    	, PetalLength FLOAT
    	, PetalWidth FLOAT
    	, Species VARCHAR(255)
    )
    
    -- Save the outputDataSet to a #temp table
    INSERT INTO #t1
    EXECUTE sp_execute_external_script
    	@language = N'R'
    	, @script = N'
    		OutputDataSet <- iris
    		'
    	, @output_data_1_name = N'OutputDataSet'
    
    SELECT *
    FROM #t1

    Thursday, February 16, 2017 1:33 AM
  • Hi Bob,

    Thanks for your response!

    We do not want to create a temp table. We need to get it in a variable then we need to use that variable for another calculation. We have only one value that need to be stored in a variable. Here in your example, the result is saved in @Output_data_1_name variable. But it's scope is only inside that EXECUTE sp_execute_external_script. We need to access it outside of the SP scope. Please suggest.

    thanks you,

    Julie

    Thursday, February 16, 2017 6:12 AM
  • Julie,

    You can use OUTPUT variables with sp_execute_external_script.

    -- https://msdn.microsoft.com/en-us/library/mt604368.aspx?f=255&MSPPError=-2147217396
    DECLARE @outputVariable NVARCHAR(255) 
    EXECUTE sp_execute_external_script
    	@language = N'R'
    	, @script = N'
    		outputVariable <- "Set from within R"
    		'
    	, @params = N'@outputVariable NVARCHAR(255) OUTPUT'
    	, @outputVariable = @outputVariable OUTPUT
    
    PRINT @outputVariable

    Hope this helps!

    Bob

    Thursday, February 16, 2017 2:22 PM