Dynamic Table Using Procedure Output

Question

I have 2 procedures p1 and p2 each giving results in format as below

exec p1----> #col1 #col2
              P-1   52
              P-2   25

EXEC p2----> #col1 #col2
              P-1   20
              P-2   2
              P-3   5

I want to call these procedure in another procedure which will execute the two procedure ,create a temp table and insert the result of the two table as shown below:

#col1 #col2 #col3
P-1    52    20
P-2    25    2
P-3    NULL  5

I am new to using Temp table any help would be appreciated. This code has to be run in SQL server. Regards,

Possible duplicate of [Insert results of a stored procedure into a temporary table](http://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table) — Vladimir Baranov, Feb 14 '17 at 06:11

score 0 · Answer 1 · answered Feb 14 '17 at 10:01

I guess this is what you want .Try once it may help you

As per your procedures output follow below query

create table #tempp (col1 varchar(50),col2 bigint)

insert into #temp
exec p1

create table #tempp1 (col1 varchar(50),col2 bigint)

insert into #tempp1
exec p2

select t1.col1,t.col2,t1.col2 from #tempp t
full join #tempp1 t1 on t.col1=t1.col1

I have created sample data as like your procedure output data and tried in my db

              select * into #tempp from (
              select 'p-1' as col1,52 col2
              union all
              select 'p-2',25
              ) as a

              select * into #tempp1 from (
              select 'p-1' as col1,20 col2
              union all
              select 'p-2',2
                union all
              select 'p-3',5
              ) as a

select t1.col1,t.col2,t1.col2 from #tempp t
full join #tempp1 t1 on t.col1=t1.col1

Your output looks like

+------+------+------+
| col1 | col2 | col2 |
+------+------+------+
| p-1  | 52   |   20 |
| p-2  | 25   |    2 |
| p-3  | NULL |    5 |
+------+------+------+

Dynamic Table Using Procedure Output

1 Answers1