将存储过程的结果保存到表变量[duplicate]中

时间:2021-10-27 02:08:04

Possible Duplicate:
How to SELECT * INTO [temp table] FROM [stored procedure]

可能的重复:如何从[存储过程]中选择[temp表]中的*

I have a nested stored procedure call

我有一个嵌套的存储过程调用

In one of the stored procedures I want to save the result into a table variable likt this :

在其中一个存储过程中,我想将结果保存到一个表变量中,如下所示:

INSERT INTO @myTable 
EXEC sp_myStoredProcedure

however, because the proc. is nested the following error occurs : An INSERT EXEC statement cannot be nested

但是,由于proc.是嵌套的,因此出现以下错误:插入EXEC语句不能嵌套

The procedure must be called from another procedure, changing this is not an option. I wanted to try to use an output parameter but it still has to be set with a Insert into statement.

必须从另一个过程调用该过程,不能更改此过程。我想尝试使用一个输出参数,但是它仍然需要设置一个Insert into语句。

What are other options to save the data that is retrieved from the call of a Stored Procedure into a variable ?

还有哪些选项可以将从存储过程调用中检索到的数据保存到变量中?

2 个解决方案

#1


3  

Table variables are not visible to the calling procedure in the case of nested procs. The following is legal with #temp tables.

在嵌套过程中,调用过程不可见表变量。对于#temp表,以下内容是合法的。

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

http://support.microsoft.com/kb/305977/en-us

http://support.microsoft.com/kb/305977/en-us

#2


-2  

Not tested but maybe do this:

没有经过测试,但可以这样做:

DECLARE @insertedProc as varchar(300)
SET @insertedProc = 'INSERT INTO ' + @myTable + ' sp_myStoredProcedure'

EXEC @insertedProc

Make sure you have defined what @myTable is before hand.

确保您事先定义了@myTable是什么。

#1


3  

Table variables are not visible to the calling procedure in the case of nested procs. The following is legal with #temp tables.

在嵌套过程中,调用过程不可见表变量。对于#temp表,以下内容是合法的。

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

http://support.microsoft.com/kb/305977/en-us

http://support.microsoft.com/kb/305977/en-us

#2


-2  

Not tested but maybe do this:

没有经过测试,但可以这样做:

DECLARE @insertedProc as varchar(300)
SET @insertedProc = 'INSERT INTO ' + @myTable + ' sp_myStoredProcedure'

EXEC @insertedProc

Make sure you have defined what @myTable is before hand.

确保您事先定义了@myTable是什么。