How do I grab multiple outputs from a stored procedure into temp table

Question

My stored procedure returns me two outputs. I want to use one of them in another stored procedure. So trying to grab the second output in a temp table. but since the structure of both the outputs are different, there fore I always get "Column name or number of supplied values does not match table definition."

Even if I change the order of the output(first output second and second output first), it is not working.

I am establishing a brand new application and I need to use the stored procedure again an d again in another stored procedures. If I face this type of situation, probably I need to rewrite lot of code.

There is a very nice questions, but this covers only one output.

Insert results of a stored procedure into a temporary table

thanks

Answer 1

You can't, not without modifying the stored procedure.

In SQL Server, you can only insert the first result set of a stored procedure into another table, via INSERT...EXEC. Column count and positions must match exactly, and INSERT...EXEC cannot be nested, ie you cannot insert from proc1 into a table in proc2, and then insert from proc2 into a table in proc3. So INSERT...EXEC is an altogether unsatisfying solution.

The workaround is modify the procedure to insert results into temporary tables defined in the calling scope, eg:

create proc get_some_data as
insert #temp1 (col1, col2) select col1, col2 from table1
insert #temp2 (colA, colB) select colA, colB from table2
go
create table #temp1 (col1 int, col2 int)
create table #temp2 (colA int, colB int)
exec get_some_data
select * from #temp1
select * from #temp2
drop table #temp1
drop table #temp2
go

If you can't modify the procedure, you are out of luck. CORRECTION: as HABO kindly pointed out, you could use the CLR to iterate the result sets. See link below for details. Not too bad if you know what you are doing, and have no other choice.

For more details on sharing data between stored procedures, see this very comprehensive article by Erland Sommarskog: http://www.sommarskog.se/share_data.html