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是什么。