One of my friends asked me if it is possible to load multiple resultsets returned from a stored procedure into a temporary table for some analysis purpose. The answer is "It depends". If all the resultsets return same number of columns then it is possible. Consider the following stored procedure
create procedure proc_testing as select 345 as number select 52345 as number select 1200 as number
create table #temp(number int)
insert into #temp(number) exec proc_testing
select * from #temp
number ----------- 345 52345 1200
Tags:
Nice article, just would like to add one more condition that if you have multiple result sets then not only the columns should be same but also the data type should be compatible.
Imran
Thanks mimran18. Thats a good point
HI Madhivanan,
I think its following the UNION ALL principle.
Please correct me if i am wrong.
Thanks
Nice article... I thought it insert only first table record. but it insert all tables record. Limitation is that data type and number of column should be same.
There is another way to handle multiple result sets in procedure, it available in SQL 2012. http://raresql.com/2012/07/02/sql-server-2012-executing-stored-procedure-with-result-sets/
nice article