[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • 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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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