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

Answer 2

You can use INSERT EXEC for this.

declare @myRetTab table (somcolumn ...) insert @myRetTab exec StoredProcName @param1 

Then use union on the table variable or variables.