你好！
所以,在存储过程中,我想做一个由参数决定的条件联合.
我怎样才能做到这一点？
这是我的“不工作”程序：
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],[VIL_ADRESS]
FROM [ADRESSES]
WHERE
ID_ADRESS = @ObligedId
END
END
所以如果@ObligedId est = a -1我想没有UNION.
我用动态varchar查询做了这个,最后我用exec执行查询.但它显然效率较低,你可以使用动态查询进行sql注入(适用于asp.net应用程序).我决定更改所有存储过程
在IF条款中不可能建立联合吗？
感谢所有答案,没有例外..
解决方法
通常要做一个基于案例的联合,你转换伪
select 1 AS A
IF @b!=-1 then
union all
select 2 as B
END IF
成
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
对于您的查询,那就变成了
SELECT
[ID_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],[VIL_ADRESS]
FROM [ADRESSES]
WHERE
ID_ADRESS = @ObligedId
AND (@ObligedId != -1)
但是,由于在此特定查询中,数据来自同一个表只是不同的过滤器,因此您可以使用OR过滤器.注意：如果您使用了UNION ALL,则不能以这种方式减少它,因为UNION ALL可能会保留重复项.对于UNION(无论如何都会删除重复项),OR减少工作正常
SELECT
[ID_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
)
