I had a problem in UNION the data from two resultsets in which one resultset suppose to be the result of a SP, unfortunately UNION does not support this..

i.e.

Select field1 From tableA
UNION
EXEC MySP

...will not work. Thanks to Geetali, for the following workaround.

create table #temptable
(field1 int )
insert #temptable exec MySP
select * from #temptable

UNION

Select num1 From tableA