Pick Return Values of Stored Procedure

Question

Hi,

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

Answer 1

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.

Answer 2

you can use this excellent answer: http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure/1228165#1228165

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.

Answer 3

Wow,

thank you guys for this quick answers. I will check them.

Bye Juergen

Answer 4

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.