title: SQL Server return table-valued variable from sp_executesql
body: <p>I need to INSERT into a table-valued variable several rows that come from a dynamically generated SELECT statement, that is INSERT INTO ... SELECT ...</p>
  
  <p>I'm trying to do it via sp_executesql.
  How to return this filled table-valued variable from the sp_executesql?</p>
  
  <pre><code>DECLARE @Data as MyTableType
  DECLARE @stmt as nvarchar(max)
  
  SET @stmt = '
  DECLARE @Data as MyTableType
  INSERT INTO @Data VALUES (''test1'',''test2'') --in fact this will be INSERT INTO @Data SELECT ...
  '
  
  execute sp_executesql @stmt, N'@Data MyTableType output', @Data = @Data output
  </code></pre>
  
  <p>Error:</p>
  
  <pre><code>The table variable "@Data" can not be passed to a stored procedure with the OUTPUT option.
  </code></pre>
  
  <p>However, it accepts the other way around, that is if @Data is used as input (replacing OUTPUT for READONLY).</p>