SQL 如何组合来自两个存储过程调用的结果集？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8669703/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I combine result sets from two stored procedure calls?
提问by sharptooth
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 0and 1and have the result sets returned by both calls combined with UNIONsemantics 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?
我如何做到这一点？
回答by brian
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回答by adyusuf
create table #table (
.....
)
insert into #table exec MyStored 0
insert into #table exec MyStored 1
select * from #table
drop table #table
回答by Andriy M
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 ... EXECstatement like below:
您可以使用INSERT ... EXEC如下语句：
INSERT INTO #YourTempTable
EXEC ('
EXEC MyStored 0;
EXEC MyStored 1;
');
The results of the two calls to MyStoredwould be UNIONed (or, rather, UNION ALLed), just like with the former method.
两次调用的结果MyStored将是 UNIONed（或者更确切地说，UNION ALLed），就像前一个方法一样。
回答by Alan Browne
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 创建表 #results(fields) 然后尝试插入到 sp2 中的 #results 调用 sp1 的结果将失败并出现此错误。将 sp2 中的临时表更改为 #result 并重试，您应该会看到现在可以正常工作了。
回答by Ray K
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
如果您可以将状态列表传递给“状态”参数，则可以创建动态 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 Articleand a MS SQL Tips Articlethat 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
编辑：参数@state 将需要是一个 nVarChar，因为您传入了一个以逗号分隔的 int 值列表
