How to Read Multiple Result Sets Returned from a SQL Server Stored Procedure in R

How to read multiple result sets returned from a SQL Server stored procedure in R

RODBC may not have a feature to retrieve multiple recordsets from a stored procedure. However, Windows ADO does have the NextRecordSet() method. Consider having R make a COM interface call to ADO using the RDCOMClient library (assuming of course you use R for Windows). In ADO's connection object, you pass the same connection string as you did in RODBC.

Below retrieves query results with ADO's GetRows() which returns a two-dimensional array translated as nested lists in R.

SQL Server (Stored Proc)

CREATE PROCEDURE MultipleResults 
AS    
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM Table1;
    SELECT * FROM Table2;
END

R (ADO Call)

library(RDCOMClient)

conn <- COMCreate("ADODB.Connection")
rst <- COMCreate("ADODB.Recordset")

conn$Open("driver={SQL Server};server=server;database=db;trusted_connection=yes;")

# FIRST QUERY RESULT
rst$Open("MultipleResults", conn)
dfList1 <- rst$GetRows()    
# RETRIEVE COLUMN NAMES
dfnames1 <- vapply(c(0:(rst[['Fields']]$Count()-1)), 
                   function(i) (rst$Fields(i)$Name()), character(1))

# SECOND QUERY RESULT
rst <- rst$NextRecordset()
dfList2 <- rst$GetRows()    
# RETRIEVE COLUMN NAMES
dfnames2 <- vapply(c(0:(rst[['Fields']]$Count()-1)), 
                   function(i) (rst$Fields(i)$Name()), character(1))   
# CLOSE OBJECTS
rst$Close(); conn$Close()

# FREE RESOURCES
rst <-  conn <- NULL
rm(rst, conn)
gc()

# CONVERT NESTED LISTS TO DATAFRAMES    
dfList1 <- lapply(dfList1, function(x) setNames(data.frame(x), dfnames1))    
df1 <- do.call(rbind, dfList1)

dfList2 <- lapply(dfList2, function(x) setNames(data.frame(x), dfnames2))      
df2 <- do.call(rbind, dfList2)

SQL Server R multiple result sets

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.

Retrieve data from stored procedure which has multiple result sets

It seems like there's no good simple way to do this, without a hack or a major paradigm shift. It looks like the best way is to just split out the original procs and end up with one more proc than before:

Old way:

create procedure dbo.GetSomething
as
begin
    select * from dbo.Person;
    select * from dbo.Car;
end;

New way:

create procedure dbo.GetPeople
as
begin
    select * from dbo.Person;
end;

create procedure dbo.GetCars
as
begin
    select * from dbo.Car;
end;

-- This gives the same result as before
create procedure dbo.GetSomething
as
begin
    exec dbo.GetPeople;
    exec dbo.GetCars;
end;

Then when I'm in a different proc and need both result sets, I'd just have to call them one at a time.

Get multiple result set with stored procedure using Entity Framework calculated column

SQL Server and the .NET Framework are based on different type systems, that is why while reading data on .NET side you need to use data mapping carefully.

Here you can find list of type mapping or type equivalents between SQL Server and .NET Framework https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings

In your question, it wasn't clear what was the type of Balance column on SQL side or what type was coming from your stored procedure. In your code, you are trying to read Balance column as Double, that is where you are getting Specified cast is not valid in sql server. According to Microsoft's document(link shared above), it is stated that column type must be Float on SQL Server side so that on .NET side you can read it as Double type.

As you are getting type cast error, I guess you are returning non Float value from database. As a solution, you can update your stored procedure to cast your Balance to Float type.

Sample usage: SELECT CAST(Balance AS FLOAT)

Finally, just as a suggestion, wrap your Connection and Reader objects with using statement so that at the end of your execution, they are disposed, not to leave open connections, which eventually can result in all pooled connections are in use exception.

Stored Procedure returns multiple tables but EF only creates single resultset with the only First datatable result

I had a same requirement and was able to solve the by changing the function Import section in ef xml. Initially there is only one set of return type, you need to override the return type of the function import which will return two sets of record type.

Please go through the link below:
http://www.codeproject.com/Articles/675933/Returning-Multiple-Result-Sets-from-an-Entity-Fram

---

Related Topics