Getting one result from multiple store procedures

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!

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,'Branch2 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

|||Thanks again Manivannan.D.Sekaran! You are awesome!!!|||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.|||

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.