SQL存储过程中用三个可选参数过滤ProfileID列的WHERE条件实现

需求回顾 #

我们有一张Post表，结构如下：

需要在存储过程中传入三个可选参数@PageID、@GroupID、@ChannelID（未传入时默认值为0），实现以下过滤逻辑：

• 当至少一个参数值大于0时，返回ProfileID属于参数非0值集合的数据；
• 当三个参数均为0时，返回全部数据。

解决方案：两种WHERE条件写法 #

写法一：直观的多条件OR判断

这种写法逻辑清晰，容易理解，适合新手快速上手：

SELECT PostID, PostDescription, PostType, ProfileID
FROM Post
WHERE 
    -- 匹配非0的PageID
    (@PageID > 0 AND ProfileID = @PageID)
    -- 匹配非0的GroupID
    OR (@GroupID > 0 AND ProfileID = @GroupID)
    -- 匹配非0的ChannelID
    OR (@ChannelID > 0 AND ProfileID = @ChannelID)
    -- 三个参数全为0时返回所有数据
    OR (@PageID = 0 AND @GroupID = 0 AND @ChannelID = 0)

写法二：简洁的IN子句结合CASE

这种写法更紧凑，利用CASE过滤掉0值参数，再用IN匹配：

SELECT PostID, PostDescription, PostType, ProfileID
FROM Post
WHERE 
    -- 仅匹配非0参数对应的ProfileID
    ProfileID IN (
        CASE WHEN @PageID > 0 THEN @PageID END,
        CASE WHEN @GroupID > 0 THEN @GroupID END,
        CASE WHEN @ChannelID > 0 THEN @ChannelID END
    )
    -- 全0参数时返回所有数据
    OR (@PageID = 0 AND @GroupID = 0 AND @ChannelID = 0)

逻辑验证 #

咱们对应示例场景测试一下：

• 当@PageID=117、@GroupID=116、@ChannelID=16：两种写法都会匹配ProfileID为117、116、16的行；
• 当@PageID=0、@GroupID=0、@ChannelID=11：只会匹配ProfileID=11的行；
• 当@PageID=117、@GroupID=0、@ChannelID=11：匹配ProfileID为117和11的行；
• 当@PageID=0、@GroupID=0、@ChannelID=0：触发最后一个OR条件，返回所有行。

内容的提问来源于stack exchange，提问作者Sourav Mukherjee