How to combine result sets from two stored procedure calls?

Question

I have the following stored procedure

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

and I would like to call this stored procedure with @state being 0 and 1 , and have the result sets returned by both calls in conjunction with UNION semantics, so I have a new result set that has rows from both the first call, so and the second challenge.

Something like (imaginary SQL):

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

How do I achieve this?

Answer 1

This may simplify the problem, but if you have control over sp just use, not =:

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

If this is not an option, simply push the results of both sproc calls into the 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 

Answer 2

 create table #table ( ..... ) insert into #table exec MyStored 0 insert into #table exec MyStored 1 select * from #table drop table #table 

Answer 3

As an alternative to a series of such statements:

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

you can use one INSERT ... EXEC as shown below:

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

The results of the two MyStored calls will be UNIONed (or rather UNION ALLED), as in the previous method.

Answer 4

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

You can also use any technology that calls this procedure to concatenate records (i.e. when .NET adds a result set for each state you are looking at)

If you’d like to go to the state list with the 'state' parameter, you can 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 -- ie 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 may take longer to maintain if changes are needed in the future.

.

Here is the CodeProject Article and MS SQL Tips Article , which does the best job in detail

.

EDIT: param @state should be nVarChar as you are passing a list of int values ​​with commas.

Answer 5

If the stored procedure you are calling has a temporary table with the same name as the calling procedure, you will get this error.

eg. sp1 has temp table #results

sp2 create a table # results (fields) then an attempt to insert the result of calling sp1 into #results in sp2 will fail. change the temp table in sp2 to #result and try again and you will see that it works now.