Exec存储过程为动态临时表。

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和值。这个存储过程主要是由于功能原因被其他部门使用，我只会时不时地使用它进行数据检查。

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.

这显然失败。

4 个解决方案

#1

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

SELECT * FROM #TempTable

#2

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年就不一定了。您的临时表列必须与存储的Proc列匹配。

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

#3

Otherwise have a look over here, there are more options explained there: Insert results of a stored procedure into a temporary table?

否则看一下这里，这里有更多的选项:将存储过程的结果插入到临时表中?

#4

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')

从OPENROWSET中选择*到#tempTable ('SQLNCLI'， 'Server=localhost;Trusted_Connection=yes;'， 'EXEC OtherDb.DataProd.abc')

#1

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

SELECT * FROM #TempTable

#2