locked
Getting one result from multiple store procedures RRS feed

  • Question

  • I just need some expert advice on the proper approach on how to do this..

    I have 2 stored procedures and I want to get the results from the combination of the 2 with no duplicates for a report.

     

    1st stored procedure results:

    Branch   LoanBalTot   BranchName

    0             112154.23    Branch 0 name

    2                 2323.23     Branch 2 name

     

    2nd stored procedure results:

    Branch   BalPastDue   BranchName

    0                      254.23    Branch 0 name

    2                        23.23     Branch 2 name

     

    I need a result that will combine the 2:

    Branch   LoanBalTot     BalPastDue     BranchName

    0             112154.23               254.23    Branch 0 name

    2                  2323.23                23.23     Branch 2 name

     

    I don't know how to write a stored procedure that calls others stored procedures to combine the results into 1 result table.  This is probably simple but I'm too new to know if I'm taking the right approach.

     

    Thank you in advance!

    Tuesday, May 22, 2007 4:12 PM

Answers

  • The sample query may help you.(Store the Sp result in temp table then join it)..

     

     

    Code Snippet

    Create Proc dbo.#FirstSp

    as

                Select 0 [Branch],112154.23 [LoanBalTot],'Branch 0 name' [BranchName]

                Union All

                Select 2,2323.23,'Branch  2 name'

     

    Go

     

    Create Proc dbo.#SecondSp

    as

    Select 0 [Branch],254.2 [BalPastDue],'Branch 0 name' [BranchName]

    Select 2,23.23,'Branch 2 name'

     

    Go

     

    Create Proc dbo.#MergerSp

    as

     

                Create Table #firstsptable (

                            [Branch] Varchar(100) ,

                            [LoanBalTot] Varchar(100) ,

                            [BranchName] Varchar(100)

                );

               

                Insert Into #firstsptable Exec dbo.#FirstSp

               

                Create Table #secondsptable (

                            [Branch] Varchar(100) ,

                            [BalPastDue] Varchar(100) ,

                            [BranchName] Varchar(100)

                );

                Insert Into #secondsptable Exec dbo.#SecondSp

     

                Select A.Branch,A.[LoanBalTot],B.[BalPastDue],A.[Branch] From #firstsptable A Join #secondsptable B on A.Branch=B.Branch

     

    Go

     

    Exec dbo.#MergerSp

     

     

    Tuesday, May 22, 2007 4:45 PM
  • You can use multiple stored procedures. But it looks like you should convert the query from the 2 SPs into views or table-valued functions. You can then reuse them easily without sacrificing performance and manageability. The INSERT...EXEC approach is resource intensive, performs slower and harder to maintain.
    Tuesday, May 22, 2007 6:57 PM

All replies

  • The sample query may help you.(Store the Sp result in temp table then join it)..

     

     

    Code Snippet

    Create Proc dbo.#FirstSp

    as

                Select 0 [Branch],112154.23 [LoanBalTot],'Branch 0 name' [BranchName]

                Union All

                Select 2,2323.23,'Branch  2 name'

     

    Go

     

    Create Proc dbo.#SecondSp

    as

    Select 0 [Branch],254.2 [BalPastDue],'Branch 0 name' [BranchName]

    Select 2,23.23,'Branch 2 name'

     

    Go

     

    Create Proc dbo.#MergerSp

    as

     

                Create Table #firstsptable (

                            [Branch] Varchar(100) ,

                            [LoanBalTot] Varchar(100) ,

                            [BranchName] Varchar(100)

                );

               

                Insert Into #firstsptable Exec dbo.#FirstSp

               

                Create Table #secondsptable (

                            [Branch] Varchar(100) ,

                            [BalPastDue] Varchar(100) ,

                            [BranchName] Varchar(100)

                );

                Insert Into #secondsptable Exec dbo.#SecondSp

     

                Select A.Branch,A.[LoanBalTot],B.[BalPastDue],A.[Branch] From #firstsptable A Join #secondsptable B on A.Branch=B.Branch

     

    Go

     

    Exec dbo.#MergerSp

     

     

    Tuesday, May 22, 2007 4:45 PM
  • Thanks again Manivannan.D.Sekaran!  You are awesome!!!
    Tuesday, May 22, 2007 6:23 PM
  • You can use multiple stored procedures. But it looks like you should convert the query from the 2 SPs into views or table-valued functions. You can then reuse them easily without sacrificing performance and manageability. The INSERT...EXEC approach is resource intensive, performs slower and harder to maintain.
    Tuesday, May 22, 2007 6:57 PM
  • Thanks you for the insight!

     

    I've changed it to:

    ALTER PROCEDURE [dbo].[proc_LNS_BranchTotLoansToPastDue]

    AS

    Select A.[Branch]

    ,A.[BranchName]

    ,A.[LoanBalTot] AS [TotalLoans]

    ,B.[BalPastDue] AS [PastDue]

    , Case When B.[BalPastDue]>0 Then (B.[BalPastDue])/(A.[LoanBalTot])*100 Else 0 End As [Percent]

    From qryLNS_BranchLoanSummary A Join qryLNS_BranchPastDueSummary B on A.[Branch]=B.[Branch]

     

    where qryLNS_BranchLoanSummary is view 1 and qryLNS_BranchPastDueSummary is view 2. 

    Tuesday, May 22, 2007 7:48 PM