sql – 存储过程中的条件UNION

所以,在存储过程中,我想做一个由参数决定的条件联合.
我怎样才能做到这一点？

这是我的“不工作”程序：

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
)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!