SSDT-provider executing stored procedure with with multiple results

[Thorium]

If you have stored procedure like this:

create procedure myProc as
select * from table1
select * from table2
select * from table3

...the MsSql is getting the return schemas:

• Table1 goes to "ResultSet"
• Table2 goes to "ResultSet_1"
• Table3 goes to "ResultSet_2"
• ...

But SSDT provider doesn't know the return schemas, because they don't exist in dacpac. So currently it ignores all the other results and just returns the first item. Which is kind of wrong, as this is efficient SQL, to not create a connection per SQL-clause.

I guess the way to fix it would be changing the current implementation of executing the sprocs by calling the traditional provider:

member __.ExecuteSprocCommand(con, inputParameters, returnCols, values:obj array) = MSSqlServer.executeSprocCommand con inputParameters returnCols values

...and instead implement a more dynamic one. The current one does (these tables are "cursor"-returns):

use reader = com.ExecuteReader() :?> SqlDataReader
let result = SingleResultSet(retCol.Name, Sql.dataReaderToArray reader)
reader.NextResult() |> ignore
result

...but we should dynamically first add the schema tables return columns, and then return them, something like this:

use reader = com.ExecuteReader() :?> SqlDataReader
let schemaReturnParams = 
   [ yield reader.GetSchemaTable();
     while reader.NextResult() do yield reader.GetSchemaTable() ]
//then add the result sets to output parameters like here
            |> List.mapi (fun i dt ->
                match dt with
                | null -> None
                | _ -> findDbType "cursor"
                       |> Option.map (fun m ->
                            let name = if i = 0 then "ResultSet" else "ResultSet_" + (string i)
                            QueryParameter.Create(name,i,m,ParameterDirection.Output)))
// and then populate the returns
Set(schemaReturnParams |> Array.map (fun retCol ->
            let result = ResultSet(retCol.Name, Sql.dataReaderToArray reader)
            reader.NextResult() |> ignore
            result
)