兼容级别130的SQL可变列配对条件存储过程实现咨询

针对你需要将多组col2和col3配对条件转化为非动态拼接存储过程的需求，结合SQL Server兼容级别130（2016）的限制，推荐以下三种高效方案：

方案一：表值参数（最优解） #

表值参数是传递多组键值对的原生方案，无需字符串拆分或动态拼接，性能和可读性都最佳，且支持任意数量的配对（包括你提到的最多9组）。

步骤1：创建用户定义表类型 #

CREATE TYPE dbo.Col2Col3Pairs AS TABLE
(
    Col2 VARCHAR(50) NOT NULL,
    Col3 VARCHAR(50) NOT NULL
);
GO

步骤2：编写存储过程 #

CREATE PROCEDURE dbo.GetCol1ByPairs
    @Pairs dbo.Col2Col3Pairs READONLY
AS
BEGIN
    SET NOCOUNT ON;

    SELECT t.[col1]
    FROM [table] t
    INNER JOIN @Pairs p 
        ON t.[col2] = p.Col2 
        AND t.[col3] = p.Col3;
END;
GO

调用示例 #

DECLARE @InputPairs dbo.Col2Col3Pairs;
INSERT INTO @InputPairs (Col2, Col3)
VALUES ('A', '1'), ('B', '2'), ('C', '3');

EXEC dbo.GetCol1ByPairs @Pairs = @InputPairs;

查询优化器可以为该方案生成稳定的执行计划，完全规避动态SQL的潜在风险。

方案二：可选参数（适配最多9组条件） #

如果不想创建表类型，可以定义9组可选参数，通过OR结合IS NULL过滤未传入的条件：

CREATE PROCEDURE dbo.GetCol1ByOptionalParams
    @Col2_1 VARCHAR(50) = NULL, @Col3_1 VARCHAR(50) = NULL,
    @Col2_2 VARCHAR(50) = NULL, @Col3_2 VARCHAR(50) = NULL,
    @Col2_3 VARCHAR(50) = NULL, @Col3_3 VARCHAR(50) = NULL,
    @Col2_4 VARCHAR(50) = NULL, @Col3_4 VARCHAR(50) = NULL,
    @Col2_5 VARCHAR(50) = NULL, @Col3_5 VARCHAR(50) = NULL,
    @Col2_6 VARCHAR(50) = NULL, @Col3_6 VARCHAR(50) = NULL,
    @Col2_7 VARCHAR(50) = NULL, @Col3_7 VARCHAR(50) = NULL,
    @Col2_8 VARCHAR(50) = NULL, @Col3_8 VARCHAR(50) = NULL,
    @Col2_9 VARCHAR(50) = NULL, @Col3_9 VARCHAR(50) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT [col1]
    FROM [table]
    WHERE 
        (@Col2_1 IS NULL OR ([col2] = @Col2_1 AND [col3] = @Col3_1))
        OR (@Col2_2 IS NULL OR ([col2] = @Col2_2 AND [col3] = @Col3_2))
        OR (@Col2_3 IS NULL OR ([col2] = @Col2_3 AND [col3] = @Col3_3))
        OR (@Col2_4 IS NULL OR ([col2] = @Col2_4 AND [col3] = @Col3_4))
        OR (@Col2_5 IS NULL OR ([col2] = @Col2_5 AND [col3] = @Col3_5))
        OR (@Col2_6 IS NULL OR ([col2] = @Col2_6 AND [col3] = @Col3_6))
        OR (@Col2_7 IS NULL OR ([col2] = @Col2_7 AND [col3] = @Col3_7))
        OR (@Col2_8 IS NULL OR ([col2] = @Col2_8 AND [col3] = @Col3_8))
        OR (@Col2_9 IS NULL OR ([col2] = @Col2_9 AND [col3] = @Col3_9));
END;
GO

调用示例 #

EXEC dbo.GetCol1ByOptionalParams 
    @Col2_1 = 'A', @Col3_1 = '1',
    @Col2_2 = 'B', @Col3_2 = '2';

若需避免所有参数为NULL时返回全表数据，可在WHERE子句开头添加NOT (@Col2_1 IS NULL AND @Col3_1 IS NULL AND ... AND @Col2_9 IS NULL AND @Col3_9 IS NULL)。

方案三：字符串拆分优化（解决你之前的二次拆分问题） #

如果坚持使用A|1,B|2,C|3格式的参数，可通过STRING_SPLIT结合字符串函数完成拆分，无需游标：

CREATE PROCEDURE dbo.GetCol1ByStringParam
    @Param VARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    WITH SplitPairs AS (
        SELECT value AS Pair
        FROM STRING_SPLIT(@Param, ',')
        WHERE value <> ''
    ),
    ParsedPairs AS (
        SELECT 
            SUBSTRING(Pair, 1, CHARINDEX('|', Pair) - 1) AS Col2,
            SUBSTRING(Pair, CHARINDEX('|', Pair) + 1, LEN(Pair)) AS Col3
        FROM SplitPairs
        WHERE CHARINDEX('|', Pair) > 0
    )
    SELECT t.[col1]
    FROM [table] t
    INNER JOIN ParsedPairs p 
        ON t.[col2] = p.Col2 
        AND t.[col3] = p.Col3;
END;
GO

调用示例 #

EXEC dbo.GetCol1ByStringParam @Param = 'A|1,B|2,C|3';

该方案通过CTE先拆分组，再拆分每个组的|分隔符，彻底解决二次拆分的问题，同时避免了游标和动态SQL。

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