Pass table-valued parameter to a stored procedure
<p>In my project we are using Dapper to connect with database for operations.</p>

<p>I have created a stored procedure in my SQL Server database as:</p>

<pre><code>CREATE PROCEDURE dbo.usp_Check
    @TestTableType [dbo].[TestTableType] READONLY
AS
BEGIN
  SELECT 
      TestTableType.BrokerNPN,
      CASE WHEN Sales.BrokerNPN IS NULL THEN 0 ELSE 1 END as isBrokerNPNExist
  FROM 
      [dbo].[tbl_Sales] Sales
  LEFT JOIN 
      @TestTableType TestTableType ON Sales.BrokerNPN = TestTableType.BrokerNPN
END 
</code></pre>

<p>And in C# I am trying to consume my stored procedure with a TVP like this:</p>

<pre><code>public void CheckSP(List&lt;string&gt; ParamData)
{
    using (var connection = new ConnectionProvider("DbName").GetOpenConnection())
    {
        var dt = new DataTable("dbo.TestTableType");
        dt.Columns.Add("NPN", typeof(string));
        dt.Rows.Add("12345");

        // First attempt
        var result = connection.Query&lt;CheckData&gt;("usp_Check", new { BrokerNPN = ParamData }, CommandType.StoredProcedure).ToList();

        // Second attempt
        var result = connection.Query&lt;CheckData&gt;("usp_Check", new { BrokerNPN = dt}, CommandType.StoredProcedure).ToList();
    }
}
</code></pre>

<p>But I am not able to pass the TVP to the stored procedure.</p>

<p>For the first attempt, I am getting this error </p>

<blockquote>
  <p>Procedure or function CheckBrokerNPNExist has too many arguments specified</p>
</blockquote>

<p>And for the second attempt, I cannot use pass <code>DataTable</code> directly.</p>