我有2个过程proc_Data , proc_FetchData 。
我从proc_FetchData调用proc_FetchData 。 proc_Data返回2个表。 我想在第二个过程中仅将第一个表插入临时表中并进一步使用它。
问题是我无法以任何方式更改proc_Data ,因为这是我们应用程序各个部分中使用的非常旧的过程。
示例代码以供参考
create procedure proc_Data As Begin select 'Apples' select 'Oranges','Grapes' end create procedure proc_FetchData As Begin create table #temp(Data varchar(30)) insert into #temp exec Test_proc select * from #temp end我正在使用SQL Server 2014 - 有什么方法可以实现这一点? 提前致谢。
I have 2 procedures proc_Data, proc_FetchData.
I'm calling proc_Data from within proc_FetchData. proc_Data returns 2 tables. I want to insert only the first table into a temp table in my second procedure and use it further.
The problem is I cannot change proc_Data in any way as this is a very old procedure used in various parts of our application.
Sample code for reference
create procedure proc_Data As Begin select 'Apples' select 'Oranges','Grapes' end create procedure proc_FetchData As Begin create table #temp(Data varchar(30)) insert into #temp exec Test_proc select * from #temp endI'm using SQL Server 2014 - is there any way to achieve this? Thanks in advance.
最满意答案
要仅插入第一个表,您可以使用OPENROWSET。
create procedure proc_FetchData As Begin declare @userData TABLE(Data NVARCHAR(30)) INSERT INTO @userData SELECT * FROMOPENROWSET('SQLNCLI','Server=[server];Trusted_Connection=yes;','EXEC [database name].dbo.proc_Data') select * from @userData end如果您需要访问所有结果集,则需要使用SQLCLR proc: https : //social.msdn.microsoft.com/Forums/sqlserver/en-US/da5328a7-5dab-44b3-b2b1-4a8d6d7798b2/insert-into-table酮-或多结果集从-存储过程?论坛= TRANSACTSQL
To insert only the first table you can use OPENROWSET.
create procedure proc_FetchData As Begin declare @userData TABLE(Data NVARCHAR(30)) INSERT INTO @userData SELECT * FROMOPENROWSET('SQLNCLI','Server=[server];Trusted_Connection=yes;','EXEC [database name].dbo.proc_Data') select * from @userData endIf you need to access all resultsets you will need to use SQLCLR proc: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/da5328a7-5dab-44b3-b2b1-4a8d6d7798b2/insert-into-table-one-or-multiple-result-sets-from-stored-procedure?forum=transactsql
更多推荐
发布评论