• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

How to merge the results from 3 stored procedures?

Hello,

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,

Mike
0
mjgardne
Asked:
mjgardne
  • 2
1 Solution
 
louislietaerCommented:
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)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
either:
* 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)
0
 
mjgardneAuthor Commented:
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
)
AS
BEGIN
      -- 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
END
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
AS
 SELECT * FROM dbo.fnPayrollEvents1 (@StartDateTime, @EndDateTime)


in case 3:


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

CHeers

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now