57

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.

gbn
  • 422,506
  • 82
  • 585
  • 676
webworm
  • 10,587
  • 33
  • 120
  • 217

5 Answers5

89

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

gbn
  • 422,506
  • 82
  • 585
  • 676
  • Thanks for the example. Should I explicitly DROP the temp table or does SQL Server take care of dropping it itself? – webworm Mar 13 '11 at 20:33
  • 3
    It will be dropped when connection is closed, or when no longer in scope (say this code above is itself a stored proc) – gbn Mar 13 '11 at 20:42
  • for more robustness consider using table variables instead of # tables. `declare @myRetTab table (somcolumn ...) ` – Ben Jan 17 '12 at 12:28
  • @Ben_ It makes no difference here. – gbn Jan 17 '12 at 12:45
  • Ben, table variables are bad for query optimization, gbn.....ur answers are the best always – Bill Blankenship Apr 12 '13 at 21:50
  • Since it's a bit annoying to manually do the correct column definition you can do something like this `SELECT name, system_type_name FROM sys.dm_exec_describe_first_result_set ('sp_fkeys ''''', NULL, 0);` as described in https://stackoverflow.com/a/31298182 to get the proper column names and types – CervEd Nov 17 '21 at 10:21
  • To be clear the original OP question had UNION and this is in effect a UNION ALL type of result during the inserts - so you need to do a `SELECT DISTINCT` on the `#foo` for that result which a `UNION` provides. – Mark Schultheiss Aug 18 '23 at 14:27
12

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.

Ben
  • 34,935
  • 6
  • 74
  • 113
3

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.

Transact Charlie
  • 2,195
  • 17
  • 14
  • 17
    The OP specifically says "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" this answer may be right but completely fails to acknowlege that part at all! – Martin Smith Mar 14 '11 at 13:02
  • 1
    The OP described the stored procedures as black box. Rewriting the stored procedures is clearly not an option for the OP. – WonderWorker Jul 21 '15 at 10:42
0

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)
Vikrant Shitole
  • 506
  • 10
  • 19
0

Hello I had this same issue. I found the solution here If you need to so something like this (this will not work) :

exec test1 'Variable1', 'Variable2';
UNION
exec test2 'Variable1', 'Variable2;

How to do the union of the 2 store procedures:

  1. Create a variable as table.

  2. Define all the columns that will return the store procedure in the variables of the table.

  3. Make the union of the variables. --First variable

    declare @table1 as table ( 
    column1 nvarchar(20) ,   
    column2 nvarchar(30),
    column3 nvarchar(50)   )

--Second variable

  declare @table2 as table ( 
       column1 nvarchar(20) ,   
       column2 nvarchar(30),
       column3 nvarchar(50)  )Insert into @table1

--Input the data returned from the store procedure into the variables

exec test1 'Variable1', 'Variable2';
Insert into @table2
exec test2 'Variable1', 'Variable2';

--Make the union with the variables

   select * from @table1 
    UNION 
   select * from @table2

I hope this post will help the next colleagues that will find this as an issue.

Victor Pellicer Climent
  • 1
  • 1