Introduction

Saving procedure result sets into a table is usually not a problem; you can use a simple INSERT INTO statement like this:

INSERT into ResultTable
 EXECUTE [AdventureWorks].[dbo].[uspGetEmployeeManagers] 90

But this only works if the stored procedure has exactly one result set. When the procedure contains multiple SELECT statements or multiple procedure calls, it returns multiple result sets. In such cases there is no easy way to save them into database tables.

The following article describes a Common Language Runtime (CLR) procedure that saves the multiple result sets of any given stored procedure into new database tables.

Setup

We will use SQL Server 2008 and an AdventureWorks sample database which can be downloaded from here:  http://msftdbprodsamples.codeplex.com/. There are several stored procedures in this database by default. Let’s create a procedure which returns multiple result sets:

USE [AdventureWorks]
GO
create procedure [dbo].[GetManagersAndEmployees]
 @employeeId int
as
begin
EXECUTE [AdventureWorks].[dbo].[uspGetEmployeeManagers]
   @EmployeeID
EXECUTE [AdventureWorks].[dbo].[uspGetManagerEmployees]
   @EmployeeID
end
GO

This procedure returns lists of managers and subordinates for a specific employee. For middle-level manager, it returns 2 not empty data sets:

EXECUTE [AdventureWorks].[dbo].[GetManagersAndEmployees] 90

In order to work with the SQLCLR, we need to enable it on the server:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Now the preparations are finished, and we are ready to begin.

Creating the CLR procedure

In Visual Studio, click File -> New -> Project, and then select Visual C# SQL CLR Database Project. If there is no such template in the project creation window, try to run devenv.exe with the /installvstemplates switch.

Do not forget to change the .NET framework version to v3.5. The CLR assembly created under .Net 4.0 will fail to install into the SQL Server 2008 instance.

Add a stored procedure to the newly created project by selecting Project -> Add stored procedure. The procedure will take 2 SqlString parameters. The first parameter is the target procedure call script, like ‘EXECUTE [AdventureWorks].[dbo].[GetManagersAndEmployees] 90’. The second parameter is the list of target tables, separated by commas, like ‘dbo.FirstTable,dbo.SecondTable’.

The following code converts the first parameter into a string, and the second parameter – into a List<string>:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SaveMultipleResultSet(SqlString procedureWithParameters, SqlString outputTablesNames)
    {
        // Process parameters
        string queryString = procedureWithParameters.ToString();
        string[] TablesNamesArray = ((string)outputTablesNames).Split(',');
        List<string> TablesNamesList = new List<string>(TablesNamesArray);
        for (int i = 0; i < TablesNamesList.Count; i++)
            TablesNamesList = TablesNamesList.Trim();

The next step is to connect to SQL server, execute the target procedure and save the results into a memory variable:

using (SqlConnection conn = new SqlConnection("context connection=true"))
{
  conn.Open();
  // Put procedure result sets into DataSet
  SqlDataAdapter adapter = new SqlDataAdapter(queryString, conn);
  DataSet procedureResults = new DataSet();
  adapter.Fill(procedureResults);

A DataSet is a very useful tool for our purpose, because it stores all the result sets inside its Tables collection. Now we can access the first result set by using procedureResults.Tables[0], the second result set by using procedureResults.Tables[1], and so on. For each table in this collection, we need to create a new table in the database and insert corresponding data into it.

Obviously, we can only create a database table if we have a name for it. So, we will not process the number of tables more times than the number of names we get from the outputTablesNames parameter:

int maxNum = TablesNamesList.Count;
if (procedureResults.Tables.Count < maxNum)
  maxNum = procedureResults.Tables.Count;

For each table name: if we already have such a table in the database, drop it.

for (int i = 0; i < maxNum; i++)
{
  // For each result set:
  // 1. If corresponding table already exists, drop it
  queryString = "IF OBJECT_ID(N'" + TablesNamesList + "', N'U') IS NOT NULL ";
  queryString += "DROP TABLE " + TablesNamesList + ";";
  SqlCommand command = new SqlCommand(queryString, conn);
  command.ExecuteNonQuery();

Create a new table using the table metadata. We will have to cast .NET data types to SQL data types manually in the GetDataType function (described further).

// 2. Create new table
queryString = "CREATE TABLE " + TablesNamesList + " (";
foreach (DataColumn dc in procedureResults.Tables.Columns)
{
    queryString += dc.ColumnName + " " + GetDataType(dc.DataType.ToString()) + ",";
}
if (queryString.EndsWith(","))
    queryString = queryString.Remove(queryString.Length - 1);
queryString += ");";
command.CommandText = queryString;
command.ExecuteNonQuery();

Finally, we need to insert corresponding data into the newly created table. My first intention was to use bulk copy, because it is very simple to do it in .NET code. Unfortunately, this code raised an exception:

// WARNING: This code will not work!
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
{
    bulkcopy.DestinationTableName = TablesNamesList;
    bulkcopy.WriteToServer(procedureResults.Tables);
}

It turned out that bulk copy can’t be used in the context connection. I had to insert data into the table row by row, using a simple INSERT INTO. I used a parameterized SqlCommand. For each row the Query string was the same and looked like ‘INSERT INTO targetable VALUES(@p0, @p1, @p2)’. In this string, the @p0, @p1, @p2  parameters values are different for each row.

Next we need to generate the query string with this code:

// 3. Insert data into new table
queryString = "INSERT INTO " + TablesNamesList + " VALUES (";
for (int j = 0; j < procedureResults.Tables.Columns.Count; j++ )
    queryString += "@p" + j.ToString() + ",";
if (queryString.EndsWith(","))
    queryString = queryString.Remove(queryString.Length - 1);
queryString += ");";
command.CommandText = queryString;

For each row, we need to specify the parameters values and execute an INSERT command:

foreach (DataRow row in procedureResults.Tables.Rows)
{
    command.Parameters.Clear();
    for (int j = 0; j < procedureResults.Tables.Columns.Count; j++ )
        command.Parameters.AddWithValue("@p" + j.ToString(), row[j]);
    command.ExecuteNonQuery();
}}}}

That’s it; the procedure is finished.

One last piece of code is the GetDataType function, which casts .NET data types to SQL data types. They are similar, but not exactly the same, so we need the following subroutine:

public static string GetDataType(string dataType)
    {
        string DTTemp = null;
        try
        {
            switch (dataType)
            {
                case ("System.Decimal"):
                    DTTemp = "decimal(2, 18)";
                    break;
                case ("System.String"):
                    DTTemp = "nvarchar(max)";
                    break;
                case ("System.Int32"):
                    DTTemp = "Int";
                    break;
                case ("System.Int64"):
                    DTTemp = "Bigint";
                    break;
                case ("System.DateTime"):
                    DTTemp = "DateTime";
                    break;
                case ("System.Double"):
                    DTTemp = "float";
                    break;
            }
        }
        catch { }
        return DTTemp;
    }

Please note that not all possible data types are covered in this function. If your stored procedure works with different data types, you should add their names into the switch construction.

The .NET code is ready. Now select Build -> Deploy solution to deploy the CLR assembly to the SQL Server instance automatically, or install it manually like described here: http://msdn.microsoft.com/en-us/library/ms254956(v=vs.80).aspx

Using the New Procedure

You can see the new stored procedure, SaveMultipleResultSet, in the AdventureWorks stored procedures list. Let’s specify the GetManagersAndEmployees procedure we created in the beginning of this article as a target procedure, and execute SaveMultipleResultSet:

DECLARE @queryString nvarchar(max)
set @queryString = 'EXECUTE GetManagersAndEmployees 90';
DECLARE @tablesNames nvarchar(max)
set @tablesNames = 'dbo.FirstTable,dbo.SecondTable';
EXECUTE SaveMultipleResultSet @queryString, @tablesNames

After running this, two new tables should be added to the AdventureWorks database: dbo.FirstTable and dbo.SecondTable. Let’s review their contents:

select * from dbo.FirstTable
select * from dbo.SecondTable

As you can see, the results match with the values returned by the GetManagersAndEmployees procedure.

Conclusion

The SaveMultipleResultSet procedure can be used in many tasks that use multiple result set procedures, such as ETL, data consistency testing, and others. However, I would not recommend creating multiple result set procedures. Doing so requires complex workarounds, which will likely cause unexpected errors. I would recommend creating several procedures or functions, where each one returns only one result set. Such procedures and functions are much easier to work with.