SQL 将存储过程执行到动态临时表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20280111/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Exec stored procedure into dynamic temp table
提问by JAT
To my knowledge; what I want to do is not possible in sql, but it is worth asking you guys.
据我所知; 我想做的事在 sql 中是不可能的，但值得问问你们。
Lets say I have a stored procedure abc that returns columns Id and Value. This stored procedure is mainly being used by other departments for functional reasons and I will only use it every now and again for data checks.
假设我有一个存储过程 abc，它返回列 Id 和 Value。这个存储过程主要由其他部门出于功能原因使用，我只会不时地使用它来进行数据检查。
So using it as part of my stored procedure:
所以使用它作为我的存储过程的一部分：
DECLARE @tABC TABLE
(
ID INT,
Value DECIMAL(12,2)
)
INSERT INTO @tABC
EXEC OtherDb.DataProd.abc
Oky so this will work perfectly for now, but what if they change the structure of their stored procedure?
好的，现在这将完美运行，但是如果他们更改存储过程的结构怎么办？
Adding or removing a column from their stored procedure will break my code, so is there a way of making my code more flexible.
在他们的存储过程中添加或删除一列会破坏我的代码，所以有没有办法让我的代码更灵活。
My last desperate attempt went something like this:
我最后一次绝望的尝试是这样的：
WITH tempTable AS
(
EXEC OtherDb.DataProd.abc
)
SELECT ID, Value FROM tempTable
Which obviously failed miserably.
这显然失败了。
回答by Raj
SELECT * INTO #TempTable
FROM OPENROWSET
('SQLNCLI','Server=(local)\SQL2008R2;Trusted_Connection=yes;',
'EXEC OtherDb.DataProd.abc')
SELECT * FROM #TempTable
回答by Tim Melton
Insert into a temp table. I know this works in 2008 and above, not sure about 2005. Your temp table columns must match your Stored Proc columns.
插入临时表。我知道这适用于 2008 年及以上，不确定 2005 年。您的临时表列必须与您的存储过程列匹配。
create table #mytable (custid int,company varchar(50),contactname varchar(50)
, phone varchar(50),address1 varchar(50)
, address2 varchar(50),city varchar(50)
,st varchar(2),zip varchar(20))
insert into #mytable (custid,company,contactname,phone,address1,address2,city,st,zip)
exec dbo.sp_Node_CustomerList_wService @segid = 1
select * from #mytable
where custid = 5
drop table #mytable
回答by Ali Yesilli
it is better and easy way to use openrowset
这是使用 openrowset 的更好和简单的方法
SELECT * INTO #tempTable FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'EXEC OtherDb.DataProd.abc')
SELECT * INTO #tempTable FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'EXEC OtherDb.DataProd.abc')
回答by NickyvV
Otherwise have a look over here, there are more options explained there: Insert results of a stored procedure into a temporary table?
否则看看这里，那里解释了更多选项：将存储过程的结果插入临时表？
