UNION the results of multiple stored procedures

sql sql-server stored-procedures union
webworm picture webworm · Mar 13, 2011 · Viewed 82.2k times · Source

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

gbn picture gbn · Mar 13, 2011

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

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below SQL: SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_…

Read More
SQL Server - SELECT FROM stored procedure

I have a stored procedure that returns rows: CREATE PROCEDURE MyProc AS BEGIN SELECT * FROM MyTable END My actual procedure is a little more complicated, which is why a sproc is necessary. Is it possible to select the output by …

Read More