Using Union All in a Stored Proc

I've got 5 stored procedures that return recordsets and I want to create a final stored procedure that combines the data returned from those 5 stored procedure into a single recordset, the way a UNION ALL query would. For instance- usp_1 returns users in Virginia, usp_2 returns users in Maryland, and so on... and usp_All would return all users from all 5 states.

What's the best way to do this? Create a temp table and insert each recordset into it? Is there a way to use a UNION ALL with stored procedures within a stored procedure?

Thanks in advance!
BreakerM

how about putting all EXEC procs in another proc.

set nocount on
create procedure a1 as
select "test1"
go
create procedure a2 as
select "test2"
go
create procedure a3 as
select "test3"
go
create procedure a4 as
select "test4"
go

create procedure Aall as
exec a1
exec a2
exec a3
exec a4

Go

exec Aall

I'm all for keeping it simple!

I tried this, but it is only returning the records from the first stored procedure.

*UPDATE* When I execute it in the query analyzer, it returns all the data, but in separate recordsets. 5 separate windows of result sets. But when I open the sp using a cmd object and recordset, only the first one is returned. It looks like I somehow need to combine the results.

Let me show you the actual sp:

ALTER PROCEDURE dbo.usp_PICReport_Detail

@dtStartDate datetime,
@dtEndDate datetime,
@dtStartTime varchar(10),
@dtEndTime varchar(10),
@vLocation varchar(50)=NULL,
@vRegion varchar(25)=NULL,
@vProductType varchar(50)=NULL,
@vProduct varchar(50)=NULL,
@vFundingMethod varchar(50)=NULL

AS
-- Combine dates and times
set @dtStartDate = convert(datetime,@dtStartDate + ' ' + @dtStartTime)
set @dtEndDate = convert(datetime,@dtEndDate + ' ' + @dtEndTime)

exec dbo.usp_PICReport_Detail_CD
@dtStartDate,
@dtEndDate,
@dtStartTime,
@dtEndTime,
@vLocation,
@vRegion,
@vProductType,
@vProduct,
@vFundingMethod
exec dbo.usp_PICReport_Detail_CHECKING
@dtStartDate,
@dtEndDate,
@dtStartTime,
@dtEndTime,
@vLocation,
@vRegion,
@vProductType,
@vProduct,
@vFundingMethod
exec dbo.usp_PICReport_Detail_SAVINGS
@dtStartDate,
@dtEndDate,
@dtStartTime,
@dtEndTime,
@vLocation,
@vRegion,
@vProductType,
@vProduct,
@vFundingMethod
exec dbo.usp_PICReport_Detail_MM
@dtStartDate,
@dtEndDate,
@dtStartTime,
@dtEndTime,
@vLocation,
@vRegion,
@vProductType,
@vProduct,
@vFundingMethod
exec dbo.usp_PICReport_Detail_IRA
@dtStartDate,
@dtEndDate,
@dtStartTime,
@dtEndTime,
@vLocation,
@vRegion,
@vProductType,
@vProduct,
@vFundingMethod
Go

set nocount on
alter procedure Aall as
create table #table1 (name varchar(10))
insert into #table1 exec a1
insert into #table1 exec a2
insert into #table1 exec a3
insert into #table1 exec a4
select * from #table1

Go

exec Aall

Works like a charm! Thanks a lot.