Pick Return Values of Stored Procedure

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 
  exe开发者_JAVA百科c myBigFatStoredProc

Can it be done anyhow?

Bye Juergen

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.

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.

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.