Pick Return Values of Stored Procedure

Question

I have a stored procedure that returns a result with 250!!! columns.

But I only need 3 out of the 250.

I want to call the SP and put only the 3 column values in a temp table. I don't want to define a temp table with 250 columns!

This is how I would like to do it, but this doesn't work of course:

create #myTempTable (id int, value1 int, value2 int)
insert into #myTempTable 
  exec myBigFatStoredProc

Can it be done anyhow?

Bye Juergen

can you change the store procedure to a function? – Avitus Apr 01 '10 at 14:21 — Avitus, Apr 01 '10 at 14:21

score 1 · Answer 1 · answered Apr 01 '10 at 14:20

One way would be to switch the original stored procedure to a user defined function that results a TABLE. Then you could do (e.g.):

SELECT id, value1, value2
FROM dbo.fxnMyFunctionThatReturns250Columns('SomeParameter') x

The "uncomfortable" thing about this, is the scenario where you only want 3 columns will always have the overhead/hit of returning all 250 columns.

This is something that may well be worth having a separate sproc for as it could mean the 3 column scenario can result in a much more optimal execution plan.

score 0 · Accepted Answer · edited May 23 '17 at 12:01

0

you can use this excellent answer: Insert results of a stored procedure into a temporary table

In brief it uses OPENROWSET to execute the stored procedure into a #temp table that is created on the fly, without need to name and type all the columns.

edited May 23 '17 at 12:01

Community

1
1

answered Apr 01 '10 at 14:35

KM.

101,727
34
178
212

score 0 · Answer 3 · answered Apr 01 '10 at 17:45

0

This is a time to not reuse code. You only need three columns then write a proc that does that. Even if you were able to put only the three columns you want into a temp table, you are still wasting resources generating them.

answered Apr 01 '10 at 17:45

HLGEM

94,695
15
113
186

Pick Return Values of Stored Procedure

3 Answers3