Insert Into temp table from a stored procedure that returns multiple result sets

Question

Consider the following sql

A stored proc called myProc which returns two result sets. Result set 1 returns column1, column2. Result set 2 returns column 3, column 4, column 5.

The following sql will fail since the temp table only has defined 2 int columns.

Create Table #temp1(
Column1 int,
Column2 int)

insert into #temp1 exec myProc

My question is is it possible to just insert the first result set into #temp1?

http://stackoverflow.com/questions/209383/select-columns-from-result-set-of-stored-procedure — THEn, Jun 17 '11 at 16:36
possible duplicate of [How to SELECT * INTO \[temp table\] FROM \[Stored Procedure\]](http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure) — Factor Mystic, Jun 21 '11 at 14:40

score 3 · Answer 1 · answered Jun 15 '12 at 05:42

Old post, but I faced the same problem and although the answers mentioned above are a bit related, the OP's question is about SP that returns multiple sets. The only solution I could find, apart from rewriting the SP to split it into smaller SPs, was to write a SQL CLR procedure that executes the SP and returns only the required result set. The procedure gets the index of the required result set, executes a SqlCommand to run the intial T-SQL SP, then loops through a SqlDataReader results until it finds the desired result set and returns the corresponding records. The following code is part of the SQL CLR procedure:

SqlDataReader rdr = command.ExecuteReader();
int index = 0;
bool bContinue = true;
while (index < resultSetIndex.Value)
{
    if (!rdr.NextResult())
    {
        bContinue = false;
        break;
    }
    index++;
}
if (!bContinue)
    throw new Exception("Unable to read result sets.");

.......

List<SqlMetaData> metadataList = new List<SqlMetaData>();
for (int i = 0; i < rdr.FieldCount; i++)
{
    string dbTypeName = rdr.GetDataTypeName(i);
    SqlMetaData metadata;
    if (dbTypeName.ToLower().Contains("char"))
        metadata = new SqlMetaData(rdr.GetName(i), (SqlDbType)Enum.Parse(typeof(SqlDbType), dbTypeName, true), 50);
    else
        metadata = new SqlMetaData(rdr.GetName(i), (SqlDbType)Enum.Parse(typeof(SqlDbType), dbTypeName, true));
    metadataList.Add(metadata);
}
SqlDataRecord record = new SqlDataRecord(metadataList.ToArray());
object[] values = new object[rdr.FieldCount];
if (rdr.HasRows)
{
    SqlContext.Pipe.SendResultsStart(record);
    while (rdr.Read())
    {
        rdr.GetValues(values);
        record.SetValues(values);
        SqlContext.Pipe.SendResultsRow(record);
    }
    SqlContext.Pipe.SendResultsEnd();
}

+1 outside of the oversimplification of extracting the meta-data from the `SqlDataReader` and creation of the `SqlMetaData` for the `SqlDataRecord` which can lead to problems depending on what datatypes are returned, this is pretty much the only way to get a specific result set from a Stored Procedure. — Solomon Rutzky, Sep 13 '14 at 20:15

score 3 · Answer 2 · edited Aug 20 '12 at 21:58

3

There's another way

SELECT * into #temp 
  from OPENROWSET('SQLNCLI', 'Server=(local)\\(instance);Trusted_Connection=yes;',
'EXEC (database).(schema).(sproc)')

This'll insert the first resultset into #temp

edited Aug 20 '12 at 21:58

Ben

51,770
36
127
149

answered Aug 15 '12 at 02:58

jakubk

31
1

Insert Into temp table from a stored procedure that returns multiple result sets

2 Answers2

Linked