SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

tSQLt and the INSERT EXEC Problem

By Charles Silbergleith,

I became interested in the tSQLt unit testing framework my company, GoDaddy, began making Continuous Integration and Continuous Deliver (CICD) a priority. It doesn’t take me long to realize the value of having a repeatable set of tests, especially when used as part of an automated Constant Integration cycle.

After creating a number of unit tests for some simple data retrieval procedures, I wanted to see how this worked on more complex procedures. One of these was a procedure that created a prepared SQL query based on any combination of over 30 parameters. The procedure services at least 9 different applications and is executed more than 2 million times a day. Having unit test code coverage seemed like a good idea.

For those new to the tSQLt framework, you should know that the typical pattern for testing a stored procedure that produces a result set is to:

  • ASSIGN: Use a series of INSERT INTO …… VALUES(….) statements stage the data to the tables that are going to be used in the procedure
  • Create a table to hold the expected results and stage the table with the expected data
  • ACT: Execute the procedure insert the results to the temp table with code like INSERT #actual EXEC myProc_sp
  • ASSERT: Compare the expected results to the actual results and throw an error if there’s a difference

Unfortunately, SQL Server has a restriction that you cannot nest an INSERT..EXEC statement. If myProc_sp uses an INSERT..EXEC statement, “INSERT #actual EXEC myProc_sp” fails with an “INSERT..EXEC statement cannot be nested” message.

Rewriting the stored procedure to fit the framework involved a great deal of effort and risk. Instead I wanted to get around the INSERT EXEC limitation while still fitting into the typical tSQLt test pattern. I thought, if I executed the procedure in a C# SQLCLR, held the result set(s) memory, shouldn’t I be able to write the result sets out of memory to wherever I wanted?

To test my hypothesis I use the AdventureWorks2012 database, since it’s readily available and already populated with data. I’ll create an INSERT EXEC procedure using the AdventureWorks2012 stored procedure dbo.uspGetWhereUsedProductID. When dbo.uspGetWhereUsedProductID is called, it produces a details list of ProductAssemlyIds along with ComponentIds, descriptions and pricing.

I’ll create a new procedure with the INSERT..EXEC statement by calling dbo.uspGetWhereUsedProductID and capturing the result set to a table variable:

CREATE PROCEDURE [dbo].[uspGetWhereUsedProductID_InsertExec]
as
INSERT @Results
 EXEC uspGetWhereUsedProductID 
         @StartProductID =  @StartProductID,
         @CheckDate = @CheckDate;

We can then select either the detail and/or summary, depending on the value of @Summary and @Detail.

IF @Summary = 1
        SELECT [ComponentID], SUM([StandardCost]) AS [TotalStandardCost], SUM([ListPrice]) AS [TotalListPrice]
        FROM @Results
        GROUP BY ComponentId;
IF @Detail = 1
        SELECT * FROM @Results;

Now, if we try to capture only the details result set by executing:

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

We will get the INSERT..EXEC error:

Msg 8164, Level 16, State 1, Procedure uspGetWhereUsedProductID_InsertExec, Line 22

This is because an INSERT..EXEC statement cannot be nested.

Now that we have a procedure that will trigger the error, let’s take a look at a SQLCLR designed to execute the INSERT..EXEC procedure uspGetWhereUsedProductID_InsertExec.

To provide values to the SQLCLR I chose to use an XML object. Obviously other implementations are available. The root node specifies the schema and name of the stored procedure. The “parm” node contains a parameter for the procedure and a value to assign it. As many “parm” nodes can be used as needed. The output node contains the table name that will receive the result set. The order should correspond to the order in which the result sets are outputted from the procedure.

The stored procedure execProcCall calls the SQLCLR assembly with an XML parameter like:

 EXEC execProcCall @callXML= '<execute schema="dbo" proc="uspGetWhereUsedProductID_InsertExec" >
  <parm name="@StartProductID" value="1" />
  <parm name="@CheckDate" value="1/1/10"  />
  <parm name="@Summary" value="1" />
  <parm name="@Detail" value="1" />
  <output target="#Summary" />
  <output target="#Detail" />
</execute>';

How the CLR Assembly Works

First, the assembly will need to parse the XML and capture the attribute values. We’ll create a class to store the values:

namespace execProcCall.Models
{
    public class procCall
    {
        public string schemaName { get; set; }
        public string procName { get; set; }
        public List<ProcParameters> procParms { get; set; }
        public List<outTables> tblOut { get; set; }

    }
    public class ProcParameters
    {
        public string parmName { get; set; }
        public string parmValue { get; set; }
        public string parmDirection { get; set; }
    }

    public class outTables
    {
        public int resultSetSeq { get; set; }
        public string tableName { get; set; }

    }

}

By converting the @callXML parameter to an XML document, the code can traverse the nodes and capture the attribute values. First the procedure schema:

procCall procCall = new procCall();

XmlDocument xmlDoc = new XmlDocument();

xmlDoc.LoadXml(callXML);  // from @callXML

XmlNodeList xnList = xmlDoc.SelectNodes("/execute");

XmlNode node = xnList[0];

string procName = node.Attributes["proc"].Value;
string schema = node.Attributes["schema"].Value;

procCall.procName = procName;
procCall.schemaName = schema;

Next the code will get a list of output tables from the XML document:

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.

 
Total article views: 4398 | Views in the last 30 days: 4
 
Related Articles
ARTICLE

Insert Stored Procedure Results Into New Table Without Using OPENQUERY

OPENQUERY arrives with a lot of baggage. Try this alternative technique to inserting stored procedur...

FORUM

insertion of parameters

how can v insert parameters into procedure

FORUM

Insert data into table The results of stored procedure

Insert data into table The results of stored procedure

FORUM

Passing multi-valued parameter selections in to stored procedures

Problems with passing multi-valued parameter selections as a csv string in to stored procedure param...

FORUM

Auto generate insert Procedure

Auto generate insert Procedure

Tags
ms sql    
tsqlt    
unit testing    
 
Contribute