UNION the results of multiple stored procedures

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

CREATE TABLE #foo (bar int ...)INSERT #fooexec MyStoredProcedure 1INSERT #fooexec MyStoredProcedure 2INSERT #fooexec 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

You can use INSERT EXEC for this.

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

Then use union on the table variable or variables.

You can do all of that but think about what you are asking......

You want to pass multiple parameters to the sp and have it produce the same format result set for the different params. So you are, in effect, making a loop and repeatedly calling the stored proc with scalar data.

What you should do is rewrite the sp so that it can take sets of parameters and provide you with a combined result. Then you only do 1 set based operation.

You can pass table variables into an sp in 2008 as long as you make your own type up first.