如何组合来自两个存储过程调用的结果集？

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.

我想调用@state为0和1的存储过程，并将两个调用返回的结果集与UNION语义结合起来，这样我就有了一个新的结果集，它包含第一次调用和第二次调用的行。

Something like (imaginary SQL):

像（假想的SQL）：

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

How do I achieve that?

我如何实现这一目标？

5 个解决方案

#1

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

这可能会过度简化问题，但如果您可以控制sp，只需使用in而不是=：

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:

如果这不是一个选项，只需将两个sproc调用的结果推送到临时表：

/*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

#2

create table #table ( 
    .....
)

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

select * from #table

drop table #table

#3

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 ... EXEC语句，如下所示：

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.

对MyStored的两次调用的结果将是UNIONed（或者更确切地说，UNION ALLed），就像使用前一种方法一样。

#4

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)

您也可以使用任何技术调用此过程进行记录的连接（即让.NET附加您正在查看的每个州的结果集）

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

如果您可以将状态列表传递到“state”参数，则可以创建动态SQL查询

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

这是一篇CodeProject文章和一篇MS SQL Tips文章，它可以更好地了解详细信息

。

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

编辑：param @state将需要是一个nVarChar，因为你传入一个以逗号分隔的int值列表

#5

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

例如sp1有临时表#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.

sp2 create table #results（fields）然后尝试插入sp2中的#results，调用sp1的结果将失败并显示此错误。将sp2中的临时表更改为#result并再次尝试，您应该看到现在可以正常工作。

#1