Empty DataTable Causes Errors When Table-Valued Parameter Has VARBINARY Types
<p>I have a C# web application that serves as a passthrough to SQL Server; requests that detail SQL Server commands come in, we parse the request, generate the necessary .Net types and then use them to execute <code>SqlCommands</code> and such. The upshot of that is that the C# web application needs to be very flexible and really can't make too many assumptions about what a request "should" look like.</p>

<p>I recently solved a problem that was causing exceptions to be thrown when a table-valued parameter contained <code>VARBINARY</code> types. In the general case, we loop over the incoming request and construct a <code>DataTable</code> with the appropriate number of columns and rows. The data type of the columns weren't specified, and the data values were just inserted as <code>object</code>s. For <code>VARBINARY</code> types, this would result in the following error:</p>

<blockquote>
  <p>Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.</p>
</blockquote>

<p>I found <a href="https://stackoverflow.com/questions/12482687/datatable-with-a-byte-field-as-parameter-to-a-stored-procedure">this StackOverflow post</a>, and was able to solve the problem. Please see my pseudo-code below:</p>

<pre><code>for (var colNdx = 0; colNdx &lt; requestTvp.Columns.Count; ++colNdx)
{
    myDataTable.Columns.Add();

    if (requestTvp.Rows.Empty)
    {
        continue;
    }

    if (requestTvp.Rows[0].Columns[colNdx].DataType == Bytes)
    {
        myDataTable.Columns[colNdx].DataType = typeof(SqlBinary);
    }
}
</code></pre>

<p>From what I can tell, this is working great. The problem occurs when the incoming TVP has no rows since I have no data to check against. In this case, we just construct a <code>DataTable</code> with the correct number of columns (this is specified in the request metadata about the TVP), but these columns have no explicitly set data type. The <code>DataTable</code> has no rows in this case. This results in the same exception:</p>

<blockquote>
  <p>Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.</p>
</blockquote>

<p>Here is the full stack trace:</p>

<pre><code>System.Data.SqlClient.SqlException (0x80131904): Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query. The data for table-valued parameter "@tvp" doesn't conform to the table type of the parameter.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean&amp; dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task&amp; task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task&amp; task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
</code></pre>

<p>Honestly, I don't understand why the problem is occurring at all...if there's no data in the <code>DataTable</code> what is even causing the invalid implicit conversion?</p>

<p>What makes things even more frustrating, is that this error occurs before the request even gets to my database, so I'm not able to use SQL Server Profiler to figure out exactly what is being sent to the database server.</p>

<p><strong>So here's my question</strong>: how do I correctly pass an empty <code>DataTable</code> as table-valued parameter to a stored procedure when the corresponding User-Defined Table Type has a <code>VARBINARY</code> field? The only information I have about the table-valued parameter when I'm constructing the <code>DataTable</code> is the name of the User-Defined Table Type, the number of columns it contains, the name of the parameter in the stored procedure and the values contained in the request TVP, which in this case is essentially an empty set.</p>