将存储过程执行到动态临时表中 [英] Exec stored procedure into dynamic temp table

问题描述

据我所知；我想要做的在sql中是不可能的，但值得问问你们.

To my knowledge; what I want to do is not possible in sql, but it is worth asking you guys.

假设我有一个存储过程 abc，它返回列 Id 和 Value.这个存储过程主要是因为功能原因被其他部门使用，我只会偶尔使用它来检查数据.

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.

因此将其用作我的存储过程的一部分:

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

显然失败得很惨.

推荐答案

SELECT * INTO #TempTable 
FROM OPENROWSET
('SQLNCLI','Server=(local)\SQL2008R2;Trusted_Connection=yes;',
     'EXEC OtherDb.DataProd.abc')

SELECT * FROM #TempTable

这篇关于将存储过程执行到动态临时表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！