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
– adrianm, Commented Nov 29, 2013 at 7:30
@adrianm Your comment about UDFs actually solved my issue. I cant believe I did not see that before. — JAT
– JAT, Commented Dec 3, 2013 at 6:13
possible duplicate of Insert results of a Stored Procedure into a Temporary Table — TT.
– TT., Commented Apr 22, 2015 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
– Adriaan Davel, Commented Jul 20, 2016 at 9:15

Raj · Accepted Answer · 2013-11-29 07:36:51Z

8

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

SELECT * FROM #TempTable

answered Nov 29, 2013 at 7:36

Raj

10.9k2 gold badges47 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JAT Over a year ago

Is enabling OPENROWSET not a security risk?

Raj Over a year ago

To a certain extent, yes. But there is no other way of meeting your requirement, AFAIK

Tanner · Accepted Answer · 2015-04-30 15:13:14Z

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, 2015 at 15:13

Tanner

22.8k9 gold badges70 silver badges89 bronze badges

answered Apr 22, 2015 at 16:29

Tim Melton

3273 silver badges6 bronze badges

2 Comments

CLS Over a year ago

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.

Tim Melton Over a year ago

I agree with the comment by: Adriaan Davel Jul 20 '16 at 9:15 See his comment above.

Giacomo1968 · Accepted Answer · 2021-10-20 01:59:49Z

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, 2021 at 1:59

Giacomo1968

25.3k11 gold badges78 silver badges106 bronze badges

answered Jul 24, 2017 at 4:31

Ali Yesilli

2,21016 silver badges16 bronze badges

1 Comment

Rohit Poudel Over a year ago

Please add some description to answer.

Community · Accepted Answer · 2017-05-23 12:18:15Z

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, 2017 at 12:18

CommunityBot

11 silver badge

answered Nov 29, 2013 at 8:33

NickyvV

1,7522 gold badges16 silver badges18 bronze badges

Collectives™ on Stack Overflow

Exec stored procedure into dynamic temp table

4 Answers 4

2 Comments

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related