title: How can I manipulate the data from a Table-Valued Parameter of a User-Defined Table Type passed into a Stored Procedure?
body: <p>I apologize in advance if this question is a little vague - I'm fairly certain it's a conceptual issue, not a problem with syntax.</p>
  
  <p>I'm using <code>MSSQL</code> (SQL Server 2008) and I have a Stored Procedure that takes a Table-Valued Parameter of a User-Defined Table Type (it is being called by a piece of .NET code which is passing in a DataTable).</p>
  
  <p>The problem I am running into is as follows:<br>
  I need to manipulate the data in that table variable, however the variable itself is READONLY (and has to be in order to be passed into the Stored Procedure, from what I understand, since it is a User-Defined Table Type).</p>
  
  <p>I've considered the obvious solution of selecting everything from the <code>@TVP</code> into a <code>#temp</code> table and manipulating #temp before doing the final insert into the destination table. However, I want to be able to call multiple stored procedures to do the manipulation on the data, which means passing the table variable back and forth between the caller and the callee. The amount of data manipulation that needs to be done is extensive, which is why I'm trying to split it up into multiple Stored Procedures. As far as I can tell, that isn't possible since the TVP has to be decorated as READONLY and <code>#temp</code> tables can't be passed as parameters to stored procedures. I considered using a global <code>##temp</code> table, however this procedure could be called multiple times simultaneously with different data sets (also why I'm not selecting it into a temporary physical table). </p>
  
  <p>Is there something I'm missing here, conceptually? What would be the best way to accomplish this? I'm willing to make it a single gargantuan stored procedure - but would prefer not to in order to keep it more maintainable. </p>
  
  <p>To Recap:</p>
  
  <p>I have P_CALLER, P_CALLEE_1 and P_CALLEE_2 (not the real names, obviously).</p>
  
  <ol>
  <li><p>P_CALLER receives @SomeTable as a TVP.   </p></li>
  <li><p>P_CALLER needs to pass @SomeTable to P_CALLEE_1 to manipulate the data, after which it passes the revised results back to P_CALLER.</p></li>
  <li><p>P_CALLER does the same thing, this time calling P_CALLEE_2, which also passes it back.  </p></li>
  <li><p>P_CALLER makes the final insert into DestinationTable.</p></li>
  </ol>
  
  <p>I've tried Googling to find an answer, but it seems like none of the results I find quite match the situation. Any advice would be appreciated.</p>