How do I combine result sets from two stored procedure calls?

Question

I have a following stored procedure

CREATE PROCEDURE [dbo].[MyStored]
   @state int
AS
   SELECT blahblahblah WHERE StoredState=@state LotsOfJoinsFollow;
RETURN 0

and I'd like to call that stored procedure with @state being 0 and 1 and have the result sets returned by both calls combined with UNION semantics so that I have a new resultset that has rows from both the first call and the second call.

Something like (imaginary SQL):

(EXEC MyStored 0) UNION (EXEC MyStored 1);

How do I achieve that?

Are you okay with having the @state be a comma delimited list? (e.g. state = '0,1') - the solution is different depending on your answer. — Ray K, Dec 29 '11 at 15:32
@Ray K: I'm not sure, I'd prefer an easy to maintain solution. — sharptooth, Dec 29 '11 at 15:36
duplicate: http://stackoverflow.com/questions/5292069/union-the-results-of-multiple-stored-procedures — goric, Dec 29 '11 at 15:53
If you move the logic into a TVF instead you can just do it as a union of 2 selects. — Martin Smith, Dec 29 '11 at 16:19

score 16 · Accepted Answer · answered Dec 29 '11 at 16:16

16

This may be oversimplifying the problem, but if you have control over the sp, just use in rather than =:

CREATE PROCEDURE [dbo].[MyStored]
AS
   SELECT blahblahblah WHERE StoredState IN (0,1) LotsOfJoinsFollow;
RETURN 0

If this is not an option, just push the results of both sproc calls into a temp table:

/*Create a table with the same columns that the sproc returns*/
CREATE TABLE #tempblahblah(blahblahblah NVARCHAR(50))

INSERT #tempblahblah ( blahblahblah )
 EXEC MyStored 0

INSERT #tempblahblah ( blahblahblah )
 EXEC MyStored 1

SELECT * FROM #tempblahblah

answered Dec 29 '11 at 16:16

brian

3,635
15
17

That only works if the procedure does not also `INSERT`, otherwise you get an error: `An INSERT EXEC statement cannot be nested.` – Tomáš Zato Jul 10 '18 at 09:10
it give an error "in database there is already an object with the name of #tempTable" – MindRoasterMir Aug 31 '21 at 14:37

score 5 · Answer 2 · answered Dec 30 '11 at 13:41

5

create table #table ( 
    .....
)

insert into #table exec MyStored 0
insert into #table exec MyStored 1

select * from #table

drop table #table

answered Dec 30 '11 at 13:41

adyusuf

806
1
11
27

score 3 · Answer 3 · answered Jan 01 '12 at 21:27

Alternatively to a series of statements like these:

INSERT INTO #YourTempTable
EXEC MyStored 0;
INSERT INTO #YourTempTable
EXEC MyStored 1;

you could use one INSERT ... EXEC statement like below:

INSERT INTO #YourTempTable
EXEC ('
  EXEC MyStored 0;
  EXEC MyStored 1;
');

The results of the two calls to MyStored would be UNIONed (or, rather, UNION ALLed), just like with the former method.

score 0 · Answer 4 · answered May 31 '16 at 10:46

If the stored procedure you are calling has a temp table with the same name as one in the calling procedure you will get this error.

e.g. sp1 has temp table #results

sp2 create table #results(fields) then trying to insert into #results in sp2 the result of calling sp1 would fail with this error. change temp table in sp2 to #result and try again and you should see this now works.

score 0 · Answer 5 · answered Dec 29 '11 at 15:56

A long way would be to create a wrapper that does this - a function that takes a list of states and adds them to a final table that would be returned.

You could also have whatever technology is calling this procedure do the concatination of records (i.e. having .NET append the result set of each state you are looking into)

If you're fine with passing in a list of states to your 'state' param, you could create a dynamic sql query

CREATE PROCEDURE [dbo].[MyStored]
   @state nvarchar(150)
AS

-- @state needs to be pre-formatted in a list for an in-clause  
--    i.e. 1,2,10   (if it was a string list, you'd need to do use double single quotes around the items - ''1'',''2'',''10''

DECLARE @SQL nVarChar(5000) = '
   SELECT blahblahblah 
   FROM LotsOfJoins
   WHERE StoredState in (' + @state + ')'


exec sp_executeSql @sql

This works great for simple procedures; although, it can get take longer to maintain if changes are needed down the road.

.

Here is a CodeProject Article and a MS SQL Tips Article that does a better job going into details

.

EDIT: The param @state will need to be a nVarChar since your passing in a comma delimited list of int values

How do I combine result sets from two stored procedure calls?

5 Answers5