I have a question that I haven't been able to find an answer to in either the
help files or Technet/MSDN searches. First a little background. I have a
number of SPs that pull the same basic data but manipulate it in different
ways (e.g. some add address information for targeted mailing lists, others
summarize the data for executive reports, etc.)

What I would like to do is create a single SP to pull the raw data and then
pull the results of that SP into a temp table within another SP to then
further refine for the different reports.

For Example:

I have a stored procedure SP_Dues_Data that gives me my dues paid for the
year in some form like:

custid, invoicedate, prodname, amount,...

and I want to have another SP such that:

Create procedure SP_Dues_Report
@param1,
@param2
AS

Create Table #TempDuesData
(custid, invoicedate, prodname, amount,...)

insert into #TempDuesData
select [results of 'exec SP_Dues_Data @param1, @param2]

do stuff
..
..
..

select * from #tempDuesData t
join CustData c
on c.custid = t.custid
..
..
..

Hopefully this gives you a picture. My reasoning is, if I need to make any
changes to the SP for the underlying data, I just need to change the one SP.
As it is now, I'm copying the updated core statements to select the data and
pasting it into multiple report SPs to get them all reporting the same data.
Is this possible? Does it even make sense to try? Am I just taking the wrong
path? I appreciate any insight.

Now if you could tell me why I have never been able to get it to work before,
I'll be set. ;). Thanks again for your insight. This will save me much time
and effort down the road.

[quoted text, click to view]