0

I need to INSERT into a table-valued variable several rows that come from a dynamically generated SELECT statement, that is INSERT INTO ... SELECT ...

I'm trying to do it via sp_executesql. How to return this filled table-valued variable from the sp_executesql?

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

Error:

The table variable "@Data" can not be passed to a stored procedure with the OUTPUT option.

However, it accepts the other way around, that is if @Data is used as input (replacing OUTPUT for READONLY).

DeanOC
  • 7,142
  • 6
  • 42
  • 56
igorjrr
  • 790
  • 1
  • 11
  • 22
  • 1
    No, not possible, as the error message states - TVPs cannot be output. Why not have @stmt have a select at the end and then `insert @Data EXEC sp_executesql @@stmt;`. (BTW [first hit for Google search "TVP output parameter"](http://stackoverflow.com/questions/19270727/cant-create-stored-procedure-with-table-output-parameter), and [third hit](http://dba.stackexchange.com/questions/13782/table-valued-parameter-as-output-parameter-for-stored-procedure).) – Aaron Bertrand Apr 02 '15 at 21:09
  • @AaronBertrand Thanks Aaron, I haven't searched for 'stored procedure', was strictly focusing on sp_executesql with output parameter. The proposed alternative worked. Thank you! – igorjrr Apr 02 '15 at 21:14

0 Answers0