Execute a Stored Procedure Record Set into a Table

[lawlerpat]

BACKGROUND:
I have a number of stored procedures used to return very detailed and wide (many columns) recordsetS for reporting.
I have found that I can execute this SP recordset into a table or temp table to then manipulate the data further using a syntax as follows:

Insert into #temp_Charges
exec charges_posted @p_one, @p_two

This works great as long as the temp table #temp_Charges has the same number of columns and same datatype as the SP. But most of the time I only need a few fields.

QUESTION: Can I execute just a select number of fields from the SP recordset into a smaller table/temp table

Thanks,
Patrick

 

[ClaireHCM]

I knwo one way to do this....There must be some other method

But you need to do some modification in the store procedure which you are going to execute.

Say you have a stored procedure called "sp1"
------------------------------------------------------
create proc sp1 @p_one, @p_two as
........
..........
/*select the columns which you want to insert into the temp table*/
select value1,value2,value3.....
------------------------------------------------------
Between the dashed line is your stored procedure programming!
Do you understand what I am talking about?

 

[lawlerpat]

Thanks but NO.
The SP is fixed and used by many reports, changing it is not the solution I am looking for.
I need a way to get a subset of the fields out of the recordset as it stands.

Patrick

 

[JamesLean]

If you can't change the SP, then there's no way to do exactly what you want, as you can't reference a stored proc in a FROM clause.

If you are using SQL 2000 then you have the option of changing it to a table-valued function. This allows you to select whatever you want from the resultant table:

SELECT col1, col4
FROM func1(@p1, @p2)

--James

 

[lawlerpat]

Thanks James,
That might work for me.

 

[SQLSister]

You should consider the cost of running a stored procedure or UDF which performs selects or calculations that you do not intend to use. It may be more work to write specific selects, but is is more efficient to only query the database for the information you need for a specific purpose. Returning 20 fields when you only need two is inefficent particulary, as is often the case with reports, some are calculated fields. Another thing to consider when using an all-purpose query of this nature is that it may contain joins unecessary for your current purpose further slowing down processing and perhaps giving you an incorrect view of the data. Reusing code is nice, but not at the cost of operational efficiency.

 

[lawlerpat]

Thank you for your comments.
I agree with your arguement in general however, that is not the option I am looking for.
I am well aware of the performance costs of executing a select against more tables than I need.
In the case of this question I will be using all the tables but not all the fields. As well, there are some performance benefits of using a compiled sql statement in a Stored procedure vs. a one off select statement.
Lastly, the SP is used by multiple generic as well as custom reports so the need to maintain data consistency out weighs any potential performance loss.

Patrick