CREATE TABLE #Detail
(ProductAssemblyId     int,
 ComponentId int,
 ComponentDesc nvarchar(50),
 TotalQuantity decimal,
 StandardCost money,
 ListPrice money,
 BOMLevel smallint,
 RecursionLevel int
);
insert #Detail
exec [dbo].[uspGetWhereUsedProductID_InsertExec]
    @StartProductID = 1,
    @CheckDate = '1/1/10',
        @Summary = 0,
        @Detail = 1

xnList = xmlDoc.SelectNodes("/execute/output");
procCall.tblOut = new List<outTables>();
// a default value for mapping the table to the result set
int Seq = 1;
foreach (XmlNode xn in xnList)
{
    targetTableName = xn.Attributes["target"].Value;
    procCall.tblOut.Add(new outTables { resultSetSeq = Seq, tableName = targetTableName });
    Seq++;
}

By default, the order in which the output target tables are list is the way that they’ll be mapped to the result set. However, if we allow the caller to specify which result set to map to this table it allows for an interesting feature I’ll mention below.

Finally the code captures the parameters and values:

xnList = xmlDoc.SelectNodes("/execute/parm");
List<ProcParameters> parms = new List<ProcParameters>();
procCall.procParms = new List<ProcParameters>();
if(xnList != null)
{  
    foreach (XmlNode xn in xnList)
    {
        attrbName = xn.Attributes["name"].Value;
        value = xn.Attributes["value"].Value;
        dir = xn.Attributes["direction"] == null ? "input" : xn.Attributes["direction"].Value;
        parms.Add(new ProcParameters { parmName = attrbName, parmValue = value, parmDirection = dir });
        procCall.procParms.Add(new ProcParameters { parmName = attrbName, parmValue = value, parmDirection = dir });
    }
}

We now have everything we need to execute the procedure and where to map the result sets to. Before we do that, let’s check to make sure the stored procedure exists, and that the collection of parameters are valid for the procedure. To do that, I have chosen to use a stored procedure which returns 1 if the procedure exists along with a list of the parameters from sys.parameters. We’ll use the list of parameters from sys.parameters against the list of parameters from the XML input and remove any parameter that isn’t in the sys.paramters list.

      SELECT @objId = o.object_id
      FROM sys.objects o WITH (NOLOCK)
      JOIN sys.schemas s WITH (NOLOCK) ON s.schema_id = o.schema_id
      WHERE o.name = @procName
      AND s.name = @schema
      IF @objId IS NOT NULL
         SET @procExists = 1;
      IF @procExists = 1
      BEGIN
         SELECT s.name + '.'+ o.name AS [procName], p.name AS [parameterName], t.name AS [dataType]
         FROM sys.objects o WITH (NOLOCK)
         JOIN sys.parameters p WITH (NOLOCK) ON p.object_id = o.object_id
         JOIN sys.schemas s WITH (NOLOCK) ON s.schema_id = o.schema_id
         JOIN sys.types t WITH (NOLOCK) ON t.user_type_id = p.user_type_id
         WHERE o.name = @procName
         AND s.name = @schema;
      END

Back in the CLR assembly, we’ll call the admin.GetProcParameters_sp to run the query and return the server side parameters for procName:

internal static bool verifyCallRequest(ref procCall CallRequest)
.
.
.
SqlDataAdapter dbProc = new SqlDataAdapter("admin.GetProcParameters_sp", conn);
dbProc.SelectCommand.CommandType = CommandType.StoredProcedure;
dbProc.SelectCommand.Parameters.AddWithValue("@schema", CallRequest.schemaName);
dbProc.SelectCommand.Parameters.AddWithValue("@procName", CallRequest.procName);
dbProc.SelectCommand.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
DataSet dsParameters = new DataSet();
retCode = dbProc.Fill(dsParameters);
// retCode = 0 if the proc does not exist
retCode = Convert.ToInt32(dbProc.SelectCommand.Parameters["@RETURN_VALUE"].Value.ToString());
if (retCode == 0)
{
    return false;
}
// list of the proc's parameters as indicated on the server
List<string> dbParmName = new List<string>(20);
// for each parameter, get the name to lower case
foreach (DataRow r in dsParameters.Tables[0].Rows)
{
    dbParmName.Add(r["parameterName"].ToString().ToLower());
}
// Get a list of the parameters not on the server side
int matchIdx;
List<ProcParameters> parmsNotMatched = new List<ProcParameters>();
foreach (ProcParameters p in CallRequest.procParms)
{
    // we're looking for the parameter that doesn't match
    matchIdx = dbParmName.FindIndex(m => m.Equals(p.parmName.ToLower()));
    if (matchIdx == -1)
    {
        parmsNotMatched.Add(p);
    }
}
// remove the parameters that don't match
foreach (ProcParameters p in parmsNotMatched)
{
    CallRequest.procParms.RemoveAll(m => m.parmName == p.parmName);
}
}

With the procedure and parameters verified against the system catalogs, the procedure is executed and the results stored in a dataset object.

DataSet dsResults = new DataSet();
string connectionString = getConnectionString();
using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlDataAdapter daExecProcCall = new SqlDataAdapter(CallRequest.schemaName + "." + CallRequest.procName, conn);
    daExecProcCall.SelectCommand.CommandType = CommandType.StoredProcedure;
    // Add parameters to the command
    foreach(ProcParameters p in CallRequest.procParms)
    {
        daExecProcCall.SelectCommand.Parameters.Add(new SqlParameter(p.parmName, p.parmValue));
    }
    daExecProcCall.Fill(dsResults);
    conn.Close();
}

Next, we’ll verify that the target tables from the <output > node of the XML exist. I use a stored procedure to do that:

   DECLARE  @objId INT,
            @tableExists INT = 0;
      IF LEFT(@tableName,1) = '#'
         SET @objId = OBJECT_ID('tempDB.dbo.' + @tableName + '')
      ELSE
      BEGIN
         SET @tablename = @schema + '.' + @tablename;
         SET @objId = OBJECT_ID(@tableName);
      END
      IF @objId IS NULL
         SET @tableExists = 0;
      ELSE
         SET @tableExists = 1;
   RETURN @tableExists

And in the SQLCLR assembly:

SqlCommand cmd = new SqlCommand("admin.VerifyTableExists_sp", conn);
int retCode;
cmd.CommandType = CommandType.StoredProcedure;
foreach (outTables tgt in CallRequest.tblOut)
{
    // add the tableName parameter and the return value capture
    if (tgt.tableName != null)
    {
        cmd.Parameters.AddWithValue("@tablename", tgt.tableName);
        cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
    }
    // get the retCode: 1=Exists; 0=Not Exist
    conn.Open();
    cmd.ExecuteNonQuery();
    retCode = Convert.ToInt32(cmd.Parameters["@RETURN_VALUE"].Value.ToString());
    conn.Close();

Later in the code, we’ll be mapping the result sets to the target tables by column name. We can get the table metadata for as each table is verified:

DataSet dsTargets = new DataSet();
if (retCode == 1)
{
    conn.Open();
    queryStr = "SELECT * FROM " + tgt.tableName + " WHERE 1 = 0";
    SqlDataAdapter daTgtTable = new SqlDataAdapter(queryStr, conn);
    daTgtTable.SelectCommand.CommandType = CommandType.Text;
    daTgtTable.Fill(dsTargets, tgt.tableName);
    conn.Close();
}

To get the data from the result set to the target tables, we’ll build and execute a series of INSERT statements. procCallResults has the stored procedure’s result sets and dsTargetTables has the target tables

int resultTableCount = procCallResults.Tables.Count;
int targetTableCount = dsTargetTables.Tables.Count;

We can index through the list of target and result set tables and find the columns common to both

while(dtIdx <= targetTableCount -1 )
{
    // get the output table name and find it in the target table list; get the resultSetSeq number
    tblName = dsTargetTables.Tables[dtIdx].TableName;
    matchedColumns = findMatchinColumns(procCallResults.Tables[dtIdx], dsTargetTables.Tables[dtIdx]);
}

To match the columns between the result set table and the target table, we’ll create a list of columns in each table and then find the matches by name:

private static List<string> findMatchinColumns(DataTable resultSet, DataTable targetTable)
foreach(DataColumn c in resultSet.Columns)
{
    resultSetColumnNames.Add(c.ColumnName);   
}
foreach (DataColumn col in targetTable.Columns)
{
    targetTableColumnNames.Add(col.ColumnName);
}
foreach(string columnName in resultSetColumnNames)
{
    matchingColumnNames.Add(targetTableColumnNames.Find(m => m.ToLower() == columnName.ToLower()));;
}
return matchingColumnName;

For each row in the result set, we’ll build an INSERT statement with the list of matching columns:

if (matchedColumns.Count > 0)
{
    sqlInsert = "INSERT " + dsTargetTables.Tables[dtIdx].TableName + " (";
    foreach(string m in matchedColumns)
    {
        sqlInsert += m + ", ";
    }
    sqlInsert = sqlInsert.TrimEnd(',', ' ');
    sqlInsert += ") ";

Next we’ll add the VALUES clause. All values are quoted, so we’re letting SQL Server make the datatype conversions.

foreach (DataRow r in procCallResults.Tables[resultToTargetMap].Rows)
sqlValues = " VALUES ( ";
foreach (string m in matchedColumns)
{
    if (DBNull.Value.Equals(r[m]))
    {
        sqlValues +=  "NULL, ";
    }
    else
    {
        sqlValues += "'" + r[m] + "', ";
    }
}
sqlValues = sqlValues.TrimEnd(',', ' ');
sqlValues += ") ";
sqlCommandText = sqlInsert + sqlValues;
tsqlWrite.CommandText = sqlCommandText;
tsqlWrite.CommandType = CommandType.Text;
rowCount = tsqlWrite.ExecuteNonQuery();

Features

The primary reason for writing the SQLCLR was to capture result set from a stored procedure that uses the INSERT EXEC clause and in a way that fits the tSQLt paradigm. To that end it has accomplished that role in my environment.

The ability to capture multiple result sets in a single execution can be useful if Unit Tests use only one or 2 tables, but could add to maintenance of more complex tests.

In addition to these features, we gain some independence from the result set column set versus the target table result set. Normally you need to match the number and datatype of the result set columns to the number and datatypes of the columns of the target table. For example, if I have a procedure myProc that returns a result set of an accountId and balance the this works fine:

CREATE table #targetTable
(accountId nvarchar(50), Balance decimal(8,3))
insert #targetTable
exec myProc

Changing the column order will result in a conversion error:

CREATE table #targetTable
(Balance decimal(8,3), accountId nvarchar(50))
GO
insert #targetTable
exec myProc

Since the SQLCLR works by mapping data from the result set to the target table by column name, the order of the columns doesn’t matter, nor does the number. If the target table has more or less columns than the result set, that doesn’t matter either.