UNION the results of multiple stored procedures
SqlSql ServerStored ProceduresUnionSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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 - Sql
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.
Solution 3 - Sql
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.
Solution 4 - Sql
Here is General query
DECLARE @sql nvarchar(MAX)
DECLARE @sql1 nvarchar(MAX)
set @sql = 'select name from abc'
set @sql1 = 'select name from xyz'
EXECUTE(@sql + ' union all ' + @sql1)