sql - UNION the results of multiple stored procedures

Question

I have a stored procedure I need to call several different times passing in different paramaters each time. I would like to collect the results as a single dataset. Is something like this possible ...

exec MyStoredProcedure 1
UNION
exec MyStoredProcedure 2
UNION
exec MyStoredProcedure 3

I tried using the syntax above but got the error ...

Incorrect syntax near the keyword 'UNION'

The stored procedures I am dealing with are pretty complex and sort of a "black box" to me, so I cannot get into the definition of the stored procedure and change anything. Any suggestions on how to gather the results together?

I am using SQL Server 2008 R2. Thanks for any help.

Answer 1

You'd have to use a temp table like this. UNION is for SELECTs, not stored procs

CREATE TABLE #foo (bar int ...)

INSERT #foo
exec MyStoredProcedure 1

INSERT #foo
exec MyStoredProcedure 2

INSERT #foo
exec MyStoredProcedure 3

...

And hope the stored procs don't have INSERT..EXEC.. already which can not be nested. Or multiple resultsets. Or several other breaking constructs