title: Efficiently filter data in a table-valued parameter in a stored procedure
body: <p>I've got a table-valued type as a parameter to a stored procedure, with anything up to thousands of rows in it. I perform several operations on this stored procedure (currently a MERGE and an INSERT), but before those I want to filter the contents of the parameter on a predicate:</p>
  
  <pre><code>@data TableValuedType READONLY
  
  MERGE INTO Table2
  USING (
      SELECT ... FROM @data
      UNION
      SELECT ... FROM @data
      UNION
      ...)
  ON ...
  WHEN NOT MATCHED THEN ....
  
  INSERT INTO Table3
      SELECT ... FROM @data
  </code></pre>
  
  <p>Is the most efficient way to do this to declare another variable of the table type, insert into that, then use that variable to merge and insert from:</p>
  
  <pre><code>DECLARE @sanitisedData TableValuedType
  INSERT INTO @sanitisedData
      SELECT ... FROM @data
      WHERE &lt;predicate&gt;
  </code></pre>
  
  <p>or to use the predicate every time I select something from @data:</p>
  
  <pre><code>MERGE INTO Table2
  USING (
      SELECT ... FROM @data WHERE &lt;predicate&gt;
      UNION
      SELECT ... FROM @data WHERE &lt;predicate&gt;
      UNION
      ...)
  ON ...
  WHEN NOT MATCHED THEN ....
  
  INSERT INTO Table3
      SELECT ... FROM @data WHERE &lt;predicate&gt;
  </code></pre>
  
  <p>or something else?</p>