UNION the results of multiple stored procedures
Solution 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
Solution 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.