5

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

Community
  • 1
  • 1
Muhammad Sharjeel Ahsan
  • 662
  • 3
  • 13
  • 28
  • Just to clarify, are you asking about a stored procedure that returns two rowsets that have different schemas? IIRC, there is no support in TSQL for accessing anything other than the first rowset returned. – HABO Dec 09 '12 at 23:30

1 Answers1

9

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

Peter Radocchia
  • 10,710
  • 2
  • 34
  • 56
  • 4
    The article cited indicates that the CLR may be used to handle multiple result sets returned by a stored procedure without altering the stored procedure. I was afraid of that, though the OP may rejoice. – HABO Dec 10 '12 at 02:12
  • 2
    @HABO, yes, you are absolutely right, in SQLCLR you could open a datareader and iterate the results sets. Not *too* bad once you know what you are doing, and if you have no other choice. – Peter Radocchia Dec 10 '12 at 02:48
  • 1
    That's a very good idea, now I do not need to rewrite the stored procedures. – Muhammad Sharjeel Ahsan Dec 10 '12 at 03:00