How to combine 2 results from SQL procedure-CodePudding

I wrote a SQL query, it is 300 line and I made this as a procedure.

I want to run two times this procedure with different parameters and then want to see all result in one table.

For example:

exec sp_xxxxx 4652,'2022-02-07 00:00:00.000',1 
// Returns 2 columns, number of rows can vary

exec sp_xxxxx 4652,'2022-02-14 00:00:00.000',1
// Returns 2 columns, number of rows can vary

I run these together, then I hope to get a result of 4 columns

// 4 column,number of rows can vary

I tried openrowset but SQL blocked.

How can I do this, I would be very happy if you can help.

CodePudding user response：

There's not enough information to provide a demonstrable solution, but the approach should be:

Create temp table #T1(col1, col2)
Create temp table #T2(col1, col2)

Insert into #T1(col1, col2) exec proc
Insert into #T2(col1, col2) exec proc

select t1.col1, t1.col2, t2.col1, t2.col2
from #T1 inner/left/full join #T2 on<criteria>

Also note that prefixing procedures with "sp" is not recommended, this is reserved by MS and indicates a Special Procedure. Choose a different prefix - or no prefix.

CodePudding user response：

Start with creating a table type than matches the output of your procedure.
For example:

CREATE TYPE XxxxxTblType AS TABLE(
 Col1 varchar(10) not null, 
 Col2 decimal(8,2) not null
);

This table type could also be used by your procedure.

Then use a variable with that table type to collect the results from the procedures. Then create a temporary table from that table variable.

declare @Xxxxx XxxxxTblType;
insert into @Xxxxx exec sp_xxxxx 4652,'2022-02-07 00:00:00.000',1;
insert into @Xxxxx exec sp_xxxxx 4652,'2022-02-14 00:00:00.000',1;

select * into #tmpXxxxx from @Xxxxx;

Now you can query the temporary table.

select * from #tmpXxxxx;