sharptooth sharptooth - 6 months ago 31
SQL Question

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

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?

Answer

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