SQL 存储过程中的条件联合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4746192/
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
Conditional UNION in stored procedure
提问by bAN
Bonjour!
你好！
So, in a stored procedure I would like to do a conditional union decided by a parameter. How can I do that?
因此，在存储过程中，我想做一个由参数决定的条件联合。我怎样才能做到这一点？
Here is my "doesn't work" procedure :
这是我的“不起作用”程序：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spp_GetAdressesList]
@OnlyLinked bit = 1,
@ObligedId int = -1
AS
BEGIN
SELECT
[ID_ADRESS]
,[ID_ENT]
,[VOI_ADRESS]
,[NUM_ADRESS]
,[BTE_ADRESS]
,[CP_ADRESS]
,[VIL_ADRESS]
FROM [ADRESSES]
WHERE
(
(VIL_ADRESS != 'NC' AND VIL_ADRESS != '--')
AND
(@OnlyLinked = 0 OR ID_ENT is not null)
)
IF (@ObligedId != -1)
BEGIN
UNION
SELECT
[ID_ADRESS]
,[ID_ENT]
,[VOI_ADRESS]
,[NUM_ADRESS]
,[BTE_ADRESS]
,[CP_ADRESS]
,[VIL_ADRESS]
FROM [ADRESSES]
WHERE
ID_ADRESS = @ObligedId
END
END
So if @ObligedId est = a -1 I would like to doesn't have the UNION.
所以如果@ObligedId est = a -1 我希望没有UNION。
I made this with a dynamic varchar query, at the end I was executing the query with an exec. But it's apparently less efficient and you can make sql injection (It is for asp.net application) with dynamic queries. I decided to change all my stored procedures
我用一个动态 varchar 查询做了这个，最后我用一个 exec 执行查询。但它显然效率较低，您可以使用动态查询进行 sql 注入（它用于 asp.net 应用程序）。我决定改变我所有的存储过程
It's not possible to do an union in a IF clause?
不能在 IF 子句中进行联合吗？
Thanks for all answers without exceptions..
感谢所有的答案，无一例外..
回答by RichardTheKiwi
Normally to do a case based union, you transform the pseudo
通常做一个基于案例的联合，你转换伪
select 1 AS A
IF @b!=-1 then
union all
select 2 as B
END IF
into
进入
select 1 AS A
union all
select 2 as B WHERE @b!=-1 -- the condition covers the entire select
-- because it is a variable test, SQL Server does it first and
-- aborts the entire part of the union if not true
For your query, that becomes
对于您的查询，这变成
SELECT
[ID_ADRESS],[ID_ENT],[VOI_ADRESS],[NUM_ADRESS],[BTE_ADRESS]
,[CP_ADRESS],[VIL_ADRESS]
FROM [ADRESSES]
WHERE
(
(VIL_ADRESS != 'NC' AND VIL_ADRESS != '--')
AND
(@OnlyLinked = 0 OR ID_ENT is not null)
)
UNION
SELECT
[ID_ADRESS],[ID_ENT],[VOI_ADRESS],[NUM_ADRESS],[BTE_ADRESS]
,[CP_ADRESS],[VIL_ADRESS]
FROM [ADRESSES]
WHERE
ID_ADRESS = @ObligedId
AND (@ObligedId != -1)
However, since in this specific query, the data is from the same table just different filters, you would OR the filters instead. Note:if you had used UNION ALL, it can not be reduced this way because of possible duplicates that UNION ALL preserves. For UNION (which removes duplicates anyway), the OR reduction works just fine
但是，由于在此特定查询中，数据来自同一个表，只是不同的过滤器，您可以使用 OR 来代替过滤器。 注意：如果您使用过 UNION ALL，则不能以这种方式减少，因为 UNION ALL 保留了可能的重复项。对于 UNION（无论如何都会删除重复项），OR 减少工作得很好
SELECT
[ID_ADRESS],[ID_ENT],[VOI_ADRESS],[NUM_ADRESS],[BTE_ADRESS]
,[CP_ADRESS],[VIL_ADRESS]
FROM [ADRESSES]
WHERE
(
(VIL_ADRESS != 'NC' AND VIL_ADRESS != '--')
AND
(@OnlyLinked = 0 OR ID_ENT is not null)
)
OR
(
ID_ADRESS = @ObligedId
AND (@ObligedId != -1) -- include this
)
回答by Andomar
You could use a whereclause to choose either end of the union:
您可以使用where子句来选择联合的任一端：
select col1, col2 from TableA where @Param = 1
UNION ALL
select col1, col2 from TableB where @Param = 2
In your example, you could omit the IFstatement entirely, since no address will have an ID_ADDRESS of -1.
在您的示例中，您可以IF完全省略该语句，因为没有地址的 ID_ADDRESS 为 -1。
回答by codingbadger
Couldn't you just rewrite your query like this:
你不能像这样重写你的查询：
SELECT
[ID_ADRESS]
,[ID_ENT]
,[VOI_ADRESS]
,[NUM_ADRESS]
,[BTE_ADRESS]
,[CP_ADRESS]
,[VIL_ADRESS]
FROM [ADRESSES]
WHERE
(
(VIL_ADRESS != 'NC' AND VIL_ADRESS != '--')
AND
(@OnlyLinked = 0 OR ID_ENT is not null)
)
Or ID_ADRESS = @ObligedId
If @obligedidis equal to -1 it won't find the Id and there for won't return the row. If it is valid id then it will return the row along with the rows returned in the first query.
如果@obligedid等于 -1，它将找不到 Id 并且不会返回该行。如果它是有效的 id，那么它将返回该行以及第一个查询中返回的行。
回答by Andrew
i'm curious would it not work to use one select and then use the two where statements but seperate them by an or like
我很好奇使用一个选择然后使用两个 where 语句但用一个或类似的分隔它们是否行不通
SELECT [ID_ADRESS],
[ID_ENT],
[VOI_ADRESS],
[NUM_ADRESS],
[BTE_ADRESS],
[CP_ADRESS],
[VIL_ADRESS]
FROM [ADRESSES]
WHERE
(
(VIL_ADRESS != 'NC' AND VIL_ADRESS != '--')
AND
(@OnlyLinked = 0 OR ID_ENT is not null)
) OR ID_ADRESS =-1
