Сохранение нескольких наборов данных, возвращаемых хранимой процедурой

Думаю многим знакома проблема, когда хранимая процедура возвращает несколько наборов данных, каждый из которых представляет интерес. Как их сохранить, вызвав хранимую процедуру? Если воспользоваться инструкцией "insert into ... exec ...", то можно сохранить в таблице лишь один набор. Для того чтобы обеспечить возможность сохранения всех наборов, которые возвращает процедура, без изменения ее кода, воспользуемся CLR-сборкой.
Пусть у нас есть такая процедура, возвращающая 2 набора:

if object_id ( N'dbo.GenerateSets', N'P' ) is null

       exec ( N'create proc dbo.GenerateSets as return' )

go

alter proc dbo.GenerateSets

as

       select 1 data1 union all select 11

       select 2 data2 union all select 22

go

Создадим временные таблицы, в которых должны сохраниться результаты хранимой процедуры после ее запуска:

if object_id ( N'tempdb..#data1', N'U' ) is not null

       drop table #data1

create table #data1 ( data int )

if object_id ( N'tempdb..#data2', N'U' ) is not null

       drop table #data2

create table #data2 ( data int )

Теперь напишем такой код на C#-е:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class ResultSets

{

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void GetAllResultSets(SqlString code, SqlString TmpTabNames)

    {

        using (SqlConnection cn = new SqlConnection("context connection = true"))

        {

            cn.Open();

            SqlCommand cmd = new SqlCommand();

            cmd.Connection = cn;

            cmd.CommandType = CommandType.Text;

            cmd.CommandText = code.ToString();

            SqlCommand Addcmd = new SqlCommand();

            Addcmd.Connection = cn;

            Addcmd.CommandType = CommandType.Text;

           

            System.Data.DataSet ds = new System.Data.DataSet("Results");

            SqlDataAdapter da = new SqlDataAdapter();

            da.SelectCommand = cmd;

            da.Fill(ds);

            string[] tmp = TmpTabNames.ToString().Split(';');

            int i = 0;

            foreach (System.Data.DataTable dt in ds.Tables)

            {

                foreach (System.Data.DataRow row in dt.Rows)

                {

                    Addcmd.CommandText = "insert into " + tmp[i] + " ( data ) values ( " + row[0].ToString() + " )";

                    Addcmd.ExecuteNonQuery();

                }

                i++;

            }

        }

    }

}

В коде этой процедуры, второй параметр является списком имен таблиц, разделенных символом точка с запятой. В этих таблицах будут сохраняться наборы записей. В процедуре вызывается запрос, текст которого передан в качестве первого параметра. Затем идет цикл по всем возвращенным наборам данных. На каждой итерации делается вставка определенного набора в соответствующую таблицу.
После компиляции развернем сборку на сервере и создадим на ее основе хранимую процедуру:

create assembly ResultSets

       from 'C:\Документы\Временные\tmp\Database9\Database9\bin\Debug\Database9.dll'

       with permission_set = safe

go

create proc dbo.SaveAllDataSets

(

       @cmd         nvarchar ( max ),

       @TmpTabs     nvarchar ( max )

)

as

       external name ResultSets.ResultSets.GetAllResultSets

go

Проверим работу сборки:
exec dbo.SaveAllDataSets 'exec dbo.GenerateSets', '#data1;#data2'

select *

from #data1

select *

from #data2

Результаты сохранены. Отмечу, что в коде сборки для простоты зашиты имена столбцов временных таблиц. А также действует предположение, что столбец один, и что он принимает целочисленное значение. Код сборки, конечно, можно обобщить, так чтобы число столбцов могло быть любым, и чтобы они могли принадлежать различным типам.