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

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')

Answer 2

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 3

The only way to acheive this is to use a hooky workaround of SELECT INTO a #temp table, which will be more trouble than it is worth.

just code the table variable with the columns that are needed. then add a very visible, well located comments into both stored procedures reminding the coder of this dependency and just move on to other endeavors.

Answer 4

CREATE PROCEDURE [dbo].[Sp_TestA](@P1 AS VARCHAR(50), @P2 AS VARCHAR(50),@P3 AS VARCHAR(50)) AS BEGIN

    SELECT '1' AS Col1,@P1 AS Col2,@P2 AS Col3,@P3 AS Col4 UNION
    SELECT '1' AS Col1,'A1' AS Col2,'A2' AS Col3,'A3' AS Col4 UNION
    SELECT '2' AS Col1,'B1' AS Col2,'B2' AS Col3,'B3' AS Col4 UNION
    SELECT '3' AS Col1,'C1' AS Col2,'C2' AS Col3,'C3' AS Col4 UNION
    SELECT '4' AS Col1,'D1' AS Col2,'D2' AS Col3,'D3' AS Col4;
END

Declare @TblRtRcs TABLE(Col1 VARCHAR(50)NOT NULL,Col2 VARCHAR(50) NOT NULL,Col3 VARCHAR(50) NOT NULL,Col4 VARCHAR(50) NOT NULL);

DECLARE @Sql AS VARCHAR(MAX);

SET @Sql='EXEC [Sp_TestA @P1=''xA'',@P2=''xB'',@P3=''xC''';

INSERT INTO @TblRtRcs(Col1,Col2,Col3,Col4) EXEC(@Sql);

SELECT * FROM @TblRtRcs;

Answer 5

This @skorpk answer actually strikes me like the correct answer to the question asked. Indeed you can insert into table variables as the question seems to indicate. Furthermore you could also do it with stored procedures that expect parameters. See sample code below:

This answer actually strikes me like the correct answer to the question asked. Indeed you can insert into table variables as the question seems to indicate. Furthermore you could also do it with stored procedures that expect parameters. See sample code below:

/*Create stored procedure for this example.       */
/*It will simulate results and we can clean it up */
/*later in this example                           */
create proc sproc_get_friends (@context_user_id int)
as
             select @context_user_id as id, 'me' as name
   union all select 1234678910 as id, 'Jane Doe' as name
   union all select 1112131415 as id, 'John Doe' as name
go

/*Create temp variable*/
declare @tmp as table ( friend_user_id int, friend_name nvarchar(100) )

/*Insert into temp variable from stored procedure*/
INSERT INTO @tmp exec  ('sproc_get_friends 10000')

/*Show data in temp variable*/
select * from @tmp
go

---Clean up
drop proc sproc_get_friends
go