CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Jinesh Sam
Jinesh Sam

Reputation: 111

How to combine two stored procedures

I am using stored procedure to get the result.

My requirement is to combine two sp result into one table.

First stored procedure is:

EXEC spDisplayBankConsolidate 'pname',@yr=2013

which returns these results :

Bankcode        Amt
Cash        13867.00
Csb         21598.50
Fd         700990.00

The second stored procedure is called like this:

EXEC spDisplayBankConsolidate 'pname', @yr=2013, @equal='Eq'`

Results:

Bankcode        Amt
Cash        13867.00
Csb          5598.50
Fd        1138049.00

My requirement is to combine the result as follows

Bankcode    Opening        Closing
Cash         13867.00        13867.00
Csb          21598.50         5598.50
Fd          700990.00      1138049.00

Upvotes: 0

Views: 3574

Answers (1)

Jeff
Jeff

Reputation: 12785

Create two temporary tables (or table variables), insert into those, then join them based on the common field (Bankcode)

Like this, I think:

create table #TableOne (BankCode nvarchar, Amt money)
create table #TableTwo (BankCode nvarchar, Amt money)

INSERT INTO #TableOne EXEC spDisplayBankConsolidate 'pname',@yr=2013
INSERT INTO #TableOne EXEC spDisplayBankConsolidate 'pname',@yr=2013,@equal='Eq'

select
    t1.BankCode, 
    t1.Amt as Opening, 
    t2.Amt as Closing
from #TableOne t1
inner join #TableTwo t2 on t1.BankCode = t2.BankCode

I made this based on the answer to this question: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d400a970-e0db-4324-9e99-9dfba214a55a/store-output-of-sql-server-exec-into-a-table?forum=transactsql

Upvotes: 3

Related Questions