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?

Solution 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

Solution 2

create table #table ( 
    .....
)

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

select * from #table

drop table #table

Solution 3

Alternatively to a series of statements like these:

INSERT INTO #YourTempTable
EXEC MyStored 0;
INSERT INTO #YourTempTable
EXEC MyStored 1;

you could use one INSERT ... EXEC statement like below:

INSERT INTO #YourTempTable
EXEC ('
  EXEC MyStored 0;
  EXEC MyStored 1;
');

The results of the two calls to MyStored would be UNIONed (or, rather, UNION ALLed), just like with the former method.

Comments

[Tomáš Zato]

That only works if the procedure does not also INSERT, otherwise you get an error: An INSERT EXEC statement cannot be nested.

[MindRoasterMir]

it give an error "in database there is already an object with the name of #tempTable"