SQL Server:将存储过程结果集放入表变量中，而不指定其模式

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.

不定义@TEMP_TABLE的模式。

6 个解决方案

#1

You cannot do this with a @tablevariable.

不能使用@tablevariable来实现这一点。

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

Joe在链接中使用SELECT…成。

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.

表变量当前不支持这一点(也不会从这个连接项的响应中得到支持)，因为表变量的模式在编译时需要知道。

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

#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.

实现这一点的唯一方法是在#temp表中使用SELECT的临时解决方案，这将比它的价值更麻烦。

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.

只需用需要的列对表变量进行编码。然后在两个存储过程中添加一个非常可见的、位置良好的注释，提醒编码器该依赖项，然后继续其他工作。

#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;

#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:

这个@skorpk的回答让我很震惊，就像这个问题的正确答案一样。实际上，您可以插入到表变量中，正如问题所示。此外，您还可以使用预期参数的存储过程来完成它。请参见下面的示例代码:

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

#6

-3

What is your problem? @TEMP_TABLE is table type. it doesn't need schema

你的问题是什么?@TEMP_TABLE是表类型。它不需要模式

#1