create proc p_test
as
-- multiple resultsets
select @@servername as ServerName;
select 'abc';
select srvname from sysservers;
go
create table #tmp(ServerName sysname)
go
INSERT #tmp
EXECUTE p_test;
I did not know that the last statement above (i.e. INSERT EXECUTE) would automatically concatenate the multiple resultsets returned from the procedure p_test and insert them into the target table, and you didn’t have to do UNION ALL explicitly. But it’s a better practice to always include UNION ALL, I think, to communicate the intention explicitly.