# Miscellaneous > Ask an Expert >  Using Union All in a Stored Proc

## BreakerM

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

----------


## MAK

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

----------


## BreakerM

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

----------


## MAK

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

----------


## BreakerM

Works like a charm!  Thanks a lot.

----------

