How to process output of multiple tables from SQL procedure?

Question

I have a procedure that generates two tables as output, but it generates an error when I run it. I want to pass both tables to local variables.

DECLARE @model_data_stats TABLE (var VARCHAR(150), center FLOAT, scale FLOAT);
DECLARE @model_log_stats TABLE (var VARCHAR(150), zero INT, plusone INT, plustwo INT);
DECLARE @model_logit VARBINARY(MAX);

EXEC sp_execute_external_script @language = N'R',
     @script = N'
       #R script, irrelevant to question
       #the following variables are assigned:
       data_stats <- table
       log_stats <- table
       trained_model <- varbinary(max) data type
     ',
     @input_data_1 = N'SELECT * FROM dbo.Table',
     @params = N'@data_stats TABLE (var VARCHAR(150), center FLOAT, scale FLOAT) OUTPUT,
                 @log_stats TABLE (var VARCHAR(150), zero INT, plusone INT, plustwo INT) OUTPUT,
                 @trained_model VARBINARY(MAX)',
     @data_stats = @model_data_stats OUTPUT,
     @log_stats = @model_log_stats OUTPUT,
     @trained_model = @model_logit OUTPUT;

This is the error I get when I run the code:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TABLE'.

It's not even especially clear to me which occurance of TABLE generates the error, but my guess is the part where the output variables are defined (after @params). The DECLARE statements by themselves run just fine.

My guess is that I'm declaring the output of the procedure in a wrong way. I'm not sure what the correct way would be. Help would be greatly appreciated!

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

After some further research I've found that what I'm trying to do here is impossible. From the MS knowledge base:

In SQL Server 2016, the output of R from the stored procedure sp_execute_external_script is limited to a single data.frame or dataset. (This limitation might be removed in future.)

However, you can return outputs of other types in addition to the dataset. For example, you might train a model using a single dataset as input, but return a table of statistics as the output, plus the trained model as an object.

How to process output of multiple tables from SQL procedure?

1 Answers1