如何向SQL存储过程的可选参数传递多组取值？

1. 先创建自定义表类型 #

表值参数需要先定义对应的用户自定义表类型，一次创建可以在所有存储过程、函数中复用：

-- 用于存储整数类型ID的通用表类型，适配状态、资金来源、项目ID等多值筛选场景
CREATE TYPE [dbo].[IntIdList] AS TABLE(
    Id INT NOT NULL PRIMARY KEY
)
GO
-- 用于存储字符串类型的通用表类型，适配多关键词筛选场景
CREATE TYPE [dbo].[StringList] AS TABLE(
    Content NVARCHAR(200) NOT NULL PRIMARY KEY
)
GO

2. 改造现有存储过程 #

替换原有固定数量的单值参数为表值参数，调整WHERE条件逻辑即可，改造后支持任意数量的筛选值传入：

CREATE OR ALTER PROCEDURE [dbo].[searchProjects_TEST] 
@keywordList StringList = NULL, -- 替换原有4个固定keyword参数，支持任意数量关键词
@projectStatusList IntIdList = NULL,
@fundingSourceList IntIdList = NULL,
@projectIdList IntIdList = NULL
AS
BEGIN
SET NOCOUNT ON;

SELECT DISTINCT project.projectID
    , projectTitle
    , topicTrackingNumber
FROM project LEFT OUTER JOIN refProjectStatus on refProjectStatus.projectStatusID = project.projectStatusID
    LEFT OUTER JOIN fundingIncrement on fundingIncrement.projectID = project.projectID
WHERE (1=1)
    AND (approvedByID IS NOT NULL)
    -- 多关键词筛选逻辑：任意关键词匹配标题、描述、目标任意一个字段即可
    AND (
        NOT EXISTS(SELECT 1 FROM @keywordList) 
        OR EXISTS(
            SELECT 1 FROM @keywordList k
            WHERE project.projectTitle LIKE k.Content 
                OR project.description LIKE k.Content 
                OR project.objective LIKE k.Content
        )
    )
    -- 多项目状态筛选，对应你需要的IN (1,3,5,9)效果
    AND (
        NOT EXISTS(SELECT 1 FROM @projectStatusList) 
        OR project.projectStatusID IN (SELECT Id FROM @projectStatusList)
    )
    -- 多资金来源筛选
    AND (
        NOT EXISTS(SELECT 1 FROM @fundingSourceList) 
        OR project.fundingSourceID IN (SELECT Id FROM @fundingSourceList)
    )
    -- 多项目ID筛选
    AND (
        NOT EXISTS(SELECT 1 FROM @projectIdList) 
        OR project.projectID IN (SELECT Id FROM @projectIdList)
    )
ORDER BY projectTitle, topicTrackingNumber
END
GO

3. 调用示例 #

需要传多个筛选值时，只需要往对应表参数插入数据即可，完全适配多选下拉框这类动态多值场景：

-- 示例：查询项目状态为1、3、5、9的项目
DECLARE @statusFilter IntIdList
INSERT INTO @statusFilter(Id) VALUES (1),(3),(5),(9)

EXEC [dbo].[searchProjects_TEST] 
    @projectStatusList = @statusFilter
GO

如果不需要某类筛选，直接不传对应参数即可，逻辑和原有单参数写法完全兼容。

内容的提问来源于stack exchange，提问作者R Loomas