title: SQL Server passing a table-valued parameters row by row
body: <p>I have a C# program that passes in a data table to a stored procedure that accepts a type of table. I need to pass the row from this stored procedure into a legacy stored procedure that accepts the values in the tables rows - not a table.</p>
  
  <p>I have the class:</p>
  
  <pre><code>public class CondemnedPart
  {
      public DateTime HarvestDate { get; set; }
      public string HouseTag2 { get; set; }
      public byte Reason { get; set; }
      public byte Product { get; set; }
  }
  </code></pre>
  
  <p>and the corresponding table-valued parameter:</p>
  
  <pre><code>CREATE TYPE Abattoir.CondemnedPartsList AS TABLE 
  (
      [UpLoadDate] [datetime] NULL,
      [HarvestDate] [date] NOT NULL,
      [Plant] [tinyint] NOT NULL,
      [Housetag2] VARCHAR(6) NOT NULL,
      Reason VARCHAR(4) NOT NULL,
      Product VARCHAR(4) NOT NULL
  )
  </code></pre>
  
  <p>So, in my mind, I need to loop through the table rows in here, and pass them to the legacy stored procedure. This seems wrong, but I'm not a SQL Server expert.
  How would I best accomplish passing the rows from the table-value into the legacy     stored procedure (which I can't change)?</p>
  
  <p>Ultimately, I need the host PC to build a large number of rows, and the SQL Server machine to process them. Row by row calling is too slow, so a table seemed like a good idea. A single call for each C# datatable.</p>
  
  <p><strong>EDIT</strong>: I am trying to adapt the old stored procedure into a new one called <code>BULK_INSERT</code> ala <a href="https://stackoverflow.com/questions/28375010/how-to-pass-values-from-a-table-parameter-in-a-stored-procedure-to-another-store">Show to Pass Values...</a>. But it doesn't seem to be working very well</p>
  
  <p><strong>EDIT 2</strong>: I am rewriting this as a stored procedure accepting a TVP of the type above. However, I do not know how to insert this into the stored procedure. For example </p>
  
  <pre><code>CREATE PROCEDURE [Abattoir].[Condemn_RecordScan_Bulk]
       @table AS Abattoir.CondemnedPartsList READONLY 
  AS
  BEGIN
      BEGIN TRY 
          BEGIN TRANSACTION
  
          SET NOCOUNT ON;
  
          DECLARE @ResolveCode TINYINT = 0;
          IF ((SELECT Housetag2 FROM @table) = '299999')
          BEGIN
              SET @HouseTag2 = NULL;
              SET @ResolveCode = NULL;
          END
  </code></pre>
  
  <p>throws a warning about my select from <code>@table</code> statement</p>