CREATE PROCEDURE Test
As
select top 10 Customerid,contactname from customers
GO
Alter PROCEDURE Test2
As
IF OBJECT_ID('tempdb..#tem1') IS NOT NULL
Drop table #tem1
Create table #tem1([Id] varchar(100),[name] varchar(100))
insert into #tem1 exec test
IF OBJECT_ID('tempdb..#tem2') IS NOT NULL
Drop table #tem2
Create table #tem2 ([Id] varchar(100),[name] varchar(100))
insert into #tem2 exec test
select * from #tem1
union all
select * from #tem2
GO