Exec stored procedure into dynamic temp table

Question

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

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

Which obviously failed miserably.

That is why you should use table valued functions or table variables instead of stored procedures to return tables. Anyway, OPENROWSET might be a solution for you — adrianm, Nov 29 '13 at 07:30
@adrianm Your comment about UDFs actually solved my issue. I cant believe I did not see that before. — JAT, Dec 03 '13 at 06:13
possible duplicate of [Insert results of a Stored Procedure into a Temporary Table](http://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table) — TT., Apr 22 '15 at 17:40
No matter how dynamic your code is if they remove columns that you use your code is broken. Do not add unnecessary overhead to your process due to poor change control. I have done a lot of dynamic code, but never for the reason of poor change control — Adriaan Davel, Jul 20 '16 at 09:15

score 8 · Answer 1 · answered Nov 29 '13 at 07:36

8

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

SELECT * FROM #TempTable

answered Nov 29 '13 at 07:36

Raj

10,653
2
45
52

Is enabling OPENROWSET not a security risk? – JAT Nov 29 '13 at 08:08
To a certain extent, yes. But there is no other way of meeting your requirement, AFAIK – Raj Nov 29 '13 at 08:15

score 6 · Answer 2 · edited Apr 30 '15 at 15:13

6

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.

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

edited Apr 30 '15 at 15:13

Tanner

22,205
9
65
83

answered Apr 22 '15 at 16:29

Tim Melton

337
3
6

The question says "Oky so this will work perfectly for now, but what if they change the structure of their stored procedure?" So this is not a good solution. – CLS Dec 07 '17 at 08:57
I agree with the comment by: Adriaan Davel Jul 20 '16 at 9:15 See his comment above. – Tim Melton Dec 08 '17 at 15:03

score 1 · Answer 3 · edited Oct 20 '21 at 01:59

1

It is better and easy way to use openrowset

SELECT * INTO #tempTable FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'EXEC OtherDb.DataProd.abc')

edited Oct 20 '21 at 01:59

Giacomo1968

25,759
11
71
103

answered Jul 24 '17 at 04:31

Ali Yesilli

2,071
13
16

Please add some description to answer. – Rohit Poudel Jul 24 '17 at 04:42

score 0 · Answer 4 · edited May 23 '17 at 12:18

0

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

edited May 23 '17 at 12:18

Community

1
1

answered Nov 29 '13 at 08:33

NickyvV

1,720
2
16
18

Exec stored procedure into dynamic temp table

4 Answers4

Linked