How to merge the results from 3 stored procedures?

Posted on 2009-05-01
Last Modified: 2012-06-21

I have three stored procedures that return the same columns (StartDate, EndDate, Duration).  I need to merge the results of these three stored procedures into a single result set, but I cannot figure out how to accomplish this goal.  I created a new stored procedure "XXX", which will be called by a report.  I've tried to select against a stored procedure, but it failed.  I've done some research and discovered that I should use EXEC to invoke a stored procedure and return the result set, but...  I cannot figure out how to merge all 3 stored procedures result sets into a single one that is returned by the new stored procedure, XXX.  I would really appreciate your help...

Thank you,

Question by:mjgardne
    LVL 5

    Expert Comment

    1) I have never this may work
    proc1 union proc2 union proc3

    2) there an another way but you will have to recode the 3 procs in one (provide the source I will do it)
    LVL 142

    Accepted Solution

    * insert the 3 procedures result into 1 temp table, and return the data from there
    * convert the procedures into functions, then you can do a union query (as functions have certain limitations, this might not work)
    * merge the 3 stored procedures into 1 (with eventually 1 optional parameter to return either all, or 1 set of what you returned previously)

    Author Closing Comment

    Thanks for the responses...  I did some research after posting my question and designed a solution very similar to the first option that was presented.  Thanks for the help and confirmation!  Here is my procedure...

    ALTER PROCEDURE [dbo].[PayrollRecords]
          @StartDateTime DateTime,
          @EndDateTime DateTime
          -- SET NOCOUNT ON added to prevent extra result sets from
          -- interfering with SELECT statements.
          SET NOCOUNT ON;

          DECLARE @PayrollData TABLE
                IsActive INTEGER,
                StartDateTime DATETIME,
                EndDateTime DATETIME,
                                    Duration DECIMAL,
                PayrollCode VARCHAR(10),
                EmployeeFullName VARCHAR(MAX)
          INSERT INTO @PayrollData EXEC dbo.PayrollEvents1 @StartDateTime, @EndDateTime
          INSERT INTO @PayrollData EXEC dbo.PayrollEvents2 @StartDateTime, @EndDateTime
          INSERT INTO @PayrollData EXEC dbo.PayrollEvents3 @StartDateTime, @EndDateTime
          SELECT * FROM @PayrollData
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    glad I could help.
    note that option 2 and option 3 are preferrable, though, IMHO.
    you could still keep the old procedures to use the new functions/combine procedure, for examle, in case 2:

    CREATE PROCEDURE dbo.PayrollEvents1 @StartDateTime datetime, @EndDateTime datetime
     SELECT * FROM dbo.fnPayrollEvents1 (@StartDateTime, @EndDateTime)

    in case 3:

    CREATE PROCEDURE dbo.PayrollEvents1 @StartDateTime datetime, @EndDateTime datetime
     -- assuming that dbo.PayrollEvents has a third parameter that specifies which results to return, actually.
     EXEC dbo.PayrollEvents @StartDateTime, @EndDateTime, 1



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now