Results 1 to 5 of 5

Thread: Using Union All in a Stored Proc

  1. #1
    Join Date
    Jun 2003
    Posts
    3

    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

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

  3. #3
    Join Date
    Jun 2003
    Posts
    3
    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
    Last edited by BreakerM; 06-25-2003 at 12:57 PM.

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

  5. #5
    Join Date
    Jun 2003
    Posts
    3
    Works like a charm! Thanks a lot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •