SQL Server R multiple result sets

Question

Is it possible to get more than one table as a result from executing R script with SQL Server 2016+? Let's take a random simple example from the internet (no need to post mine to not overcomplicate the issue):

EXEC sp_execute_external_script
  @language =N'R',
  @script=N'OutputDataSet<-InputDataSet',
  @input_data_1 =N'SELECT 1 AS hello'
  WITH RESULT SETS (([hello] int not null));

as posted in here.

Here the result is returned as a single table. Let's say I do various calculations with the data and now I want to return multiple tables as a result.

For example:

a<-InputDataSet

b<-InputDataSet + 5

These would return two different tables as results. Now I cannot figure any nice way to return the data in two separate tables as it only returns one table. Obviously, I can return it like this:

OutputDataSet<-data.frame(a, b)

But dealing with different functions and different data it soon becomes quite a hassle. For example I use a function lm. Now one dataset would be calculated estimated values and another would be the coefficients of each column participating in the equation. Again, of course I can join these two datatables and deal with them later, but the output result becomes colossal in many cases.

The parameters to the procedure look like: ..., @output_data_1_name, but there is no @output_data_2_name, etc. thus I do not see a way. Maybe it is possible to create the OutputDataSet so it holds multiple tables? If so - I am not aware of such way in R due to my lack of experience with it.

tldr; is it possible to return multiple result sets or my only solution is to manually construct the output in R code so I would always get one?

Answer 1

According to the Microsoft Documentation it is at the moment not possible to return more than one table. It states on this page:

Only one input dataset can be passed as a parameter, and you can return only one dataset.

However, you can return additional single variables, as stated here:

Generally, the output of R from the stored procedure sp_execute_external_script is limited to a single data frame. (This limitation might be removed in future.)

However, you can return outputs of other types, such as scalars, in addition to the data frame.

There is also an example given on how to do it.