SQL UNION 多个存储过程的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5292069/
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
UNION the results of multiple stored procedures
提问by webworm
I have a stored procedure I need to call several different times passing in different paramaters each time. I would like to collect the results as a single dataset. Is something like this possible ...
我有一个存储过程，我需要调用几次不同的时间，每次传入不同的参数。我想将结果作为单个数据集收集。这样的事情有可能吗...
exec MyStoredProcedure 1
UNION
exec MyStoredProcedure 2
UNION
exec MyStoredProcedure 3
I tried using the syntax above but got the error ...
我尝试使用上面的语法，但得到了错误......
Incorrect syntax near the keyword 'UNION'
The stored procedures I am dealing with are pretty complex and sort of a "black box" to me, so I cannot get into the definition of the stored procedure and change anything. Any suggestions on how to gather the results together?
我正在处理的存储过程非常复杂，对我来说就像一个“黑匣子”，所以我无法进入存储过程的定义并更改任何内容。关于如何将结果收集在一起的任何建议？
I am using SQL Server 2008 R2. Thanks for any help.
我正在使用 SQL Server 2008 R2。谢谢你的帮助。
回答by gbn
You'd have to use a temp table like this. UNION is for SELECTs, not stored procs
您必须使用这样的临时表。UNION 用于选择，而不是存储过程
CREATE TABLE #foo (bar int ...)
INSERT #foo
exec MyStoredProcedure 1
INSERT #foo
exec MyStoredProcedure 2
INSERT #foo
exec MyStoredProcedure 3
...
And hope the stored procs don't have INSERT..EXEC..already which can not be nested. Or multiple resultsets. Or several other breaking constructs
并希望存储的INSERT..EXEC..过程还没有不能嵌套的。或多个结果集。或其他几个破坏性结构
回答by Ben
You can use INSERT EXECfor this.
您可以INSERT EXEC为此使用。
declare @myRetTab table (somcolumn ...)
insert @myRetTab
exec StoredProcName @param1
Then use union on the table variable or variables.
然后在表变量或变量上使用联合。
回答by Transact Charlie
You cando all of that but think about what you are asking......
你可以做所有这些，但想想你在问什么......
You want to pass multiple parameters to the sp and have it produce the same format result set for the different params. So you are, in effect, making a loop and repeatedly calling the stored proc with scalar data.
您希望将多个参数传递给 sp 并让它为不同的参数生成相同格式的结果集。因此，您实际上是在进行循环并使用标量数据重复调用存储过程。
What you shoulddo is rewrite the sp so that it can take sets of parameters and provide you with a combined result. Then you only do 1 set based operation.
您应该做的是重写 sp，以便它可以采用参数集并为您提供组合结果。然后你只做 1 个基于集合的操作。
You can pass table variables into an sp in 2008 as long as you make your own type up first.
只要您先创建自己的类型，就可以在 2008 年将表变量传递到 sp 中。
回答by Vikrant Shitole
Here is General query
这是一般查询
DECLARE @sql nvarchar(MAX)
DECLARE @sql1 nvarchar(MAX)
set @sql = 'select name from abc'
set @sql1 = 'select name from xyz'
EXECUTE(@sql + ' union all ' + @sql1)
