SQL Server: Put stored procedure result set into a table variable without specifying its schema

Question

I have a stored procedure with many parameters.

I want to insert (or maybe select) like this:

INSERT INTO @TEMP_TABLE
    EXECUTE STORED_PROCEDURE

without defining the schema of @TEMP_TABLE.

Answer 1

You cannot do this with a @tablevariable.

The work around in the link posted by Joe uses SELECT ... INTO.

This is not currently supported by table variables (and won't ever be from the response to this connect item) as the schema for table variables needs to be known at compile time.

Answer 2

for example:

declare @temptable2 as table
(
 DatabaseName nvarchar(128),
 dbsize nvarchar(128),
 owner varchar(128),
 dbid nvarchar(128),
 created nvarchar(128),
 status nvarchar(128),
 compatibility_level nvarchar(128)
)

INSERT INTO @temptable2
EXEC ('sp_helpdb')