How do I combine result sets from two stored procedure calls?

Question

I have a following stored procedure

CREATE PROCEDURE [dbo].[MyStored]
   @state int
AS
   SELECT blahblahblah WHERE StoredState=@state LotsOfJoinsFollow;
RETURN 0

and I'd like to call that stored procedure with @state being 0 and 1 and have the result sets returned by both calls combined with UNION semantics so that I have a new resultset that has rows from both the first call and the second call.

Something like (imaginary SQL):

(EXEC MyStored 0) UNION (EXEC MyStored 1);

How do I achieve that?

Answer 1

This may be oversimplifying the problem, but if you have control over the sp, just use in rather than =:

CREATE PROCEDURE [dbo].[MyStored]
AS
   SELECT blahblahblah WHERE StoredState IN (0,1) LotsOfJoinsFollow;
RETURN 0

If this is not an option, just push the results of both sproc calls into a temp table:

/*Create a table with the same columns that the sproc returns*/
CREATE TABLE #tempblahblah(blahblahblah NVARCHAR(50))

INSERT #tempblahblah ( blahblahblah )
 EXEC MyStored 0

INSERT #tempblahblah ( blahblahblah )
 EXEC MyStored 1

SELECT * FROM #tempblahblah

Answer 2

create table #table ( 
    .....
)

insert into #table exec MyStored 0
insert into #table exec MyStored 1

select * from #table

drop table #table