INSERT INTO @table_name...EXEC usp_name

Question

I've read in a lot of web sites that the following code will not work, where @table_name is a table variable:

INSERT INTO @table_name EXEC usp_name usp_param1, usp_param2

But the above exact code works fine for me inside a stored procedure in SQL Server 2005 (version 9.0.4035).

Even MSDN (URL: http://msdn.microsoft.com/en-us/library/aa260638%28v=SQL.80%29.aspx) mentions that the insert code will not work if an attempt to club insert with exec is executed on a table variable. Unfortunately, the MSDN page on top, mentions that the page applies to SQL Server 2000, which adds to the confusion.

Would someone throw some light on this? Thanks in advance.

Answer 1

The equivalent SQL 2008/2005 MSDN page is different::

However, table cannot be used in the following statement:

SELECT select_list INTO table_variable

This is because the SELECT ... INTO creates the target table, and the create semantics are not compatible with variable scope semantics.

INSERT INTO @table EXEC ... is supported. You cannot specify a table-valued parameter, but that is different from a table variable.