如何在SQL Server 2012中实现Dynamics AX 2012式范围过滤？

核心思路 #

利用动态SQL构建符合Dynamics AX规则的WHERE子句：

• 拆分逗号分隔的条件片段
• 区分肯定/否定条件，解析通配符*（转为%）和范围..（拆分为上下限）
• 肯定条件用OR连接，否定条件用AND连接，最终组合为(肯定条件组) AND (否定条件组)

步骤1：创建字符串拆分函数（适配SQL Server 2012） #

CREATE FUNCTION dbo.SplitString
(
    @InputString NVARCHAR(MAX),
    @Delimiter NVARCHAR(5)
)
RETURNS @OutputTable TABLE (Value NVARCHAR(MAX))
AS
BEGIN
    DECLARE @StartIndex INT = 1, @EndIndex INT;

    -- 确保字符串以分隔符结尾，简化循环逻辑
    IF RIGHT(@InputString, LEN(@Delimiter)) <> @Delimiter
        SET @InputString += @Delimiter;

    WHILE CHARINDEX(@Delimiter, @InputString) > 0
    BEGIN
        SET @EndIndex = CHARINDEX(@Delimiter, @InputString);
        INSERT INTO @OutputTable(Value)
        SELECT LTRIM(RTRIM(SUBSTRING(@InputString, @StartIndex, @EndIndex - @StartIndex)));
        
        SET @InputString = SUBSTRING(@InputString, @EndIndex + LEN(@Delimiter), LEN(@InputString));
    END

    RETURN
END
GO

步骤2：创建存储过程实现动态查询 #

假设你有一个存储用户过滤条件的表UserFilters，结构如下：

CREATE TABLE UserFilters (
    FilterID INT PRIMARY KEY,
    TableName NVARCHAR(128), -- 要查询的表名
    ColumnName NVARCHAR(128), -- 要过滤的列名
    FilterCondition NVARCHAR(MAX) -- Dynamics AX风格的过滤条件，如'B*, D..H, !F*'
);

-- 插入示例过滤条件
INSERT INTO UserFilters VALUES (1, 'table1', 'column1', 'B*, D..H, !F*');

然后创建存储过程执行动态查询：

CREATE PROCEDURE GetFilteredData
    @FilterID INT
AS
BEGIN
    SET NOCOUNT ON;

    -- 获取过滤配置
    DECLARE @TableName NVARCHAR(128), @ColumnName NVARCHAR(128), @FilterCondition NVARCHAR(MAX);
    SELECT @TableName = TableName, @ColumnName = ColumnName, @FilterCondition = FilterCondition
    FROM UserFilters WHERE FilterID = @FilterID;

    -- 拆分条件片段
    DECLARE @Conditions TABLE (Value NVARCHAR(MAX));
    INSERT INTO @Conditions
    SELECT Value FROM dbo.SplitString(@FilterCondition, ',');

    -- 构建肯定/否定条件子句
    DECLARE @PositiveClauses NVARCHAR(MAX) = '', @NegativeClauses NVARCHAR(MAX) = '';
    DECLARE @Condition NVARCHAR(MAX);

    DECLARE cur CURSOR FOR SELECT Value FROM @Conditions;
    OPEN cur;
    FETCH NEXT FROM cur INTO @Condition;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @IsNegative BIT = 0;
        -- 标记并移除否定条件前缀'!'
        IF LEFT(@Condition, 1) = '!'
        BEGIN
            @IsNegative = 1;
            @Condition = LTRIM(RTRIM(SUBSTRING(@Condition, 2, LEN(@Condition))));
        END

        DECLARE @Clause NVARCHAR(MAX);
        -- 解析范围条件（含'..'）
        IF CHARINDEX('..', @Condition) > 0
        BEGIN
            DECLARE @MinVal NVARCHAR(MAX) = LTRIM(RTRIM(LEFT(@Condition, CHARINDEX('..', @Condition)-1)));
            DECLARE @MaxVal NVARCHAR(MAX) = LTRIM(RTRIM(SUBSTRING(@Condition, CHARINDEX('..', @Condition)+2, LEN(@Condition))));
            
            IF @IsNegative = 0
                @Clause = QUOTENAME(@ColumnName) + ' >= ''' + REPLACE(@MinVal, '''', '''''') + ''' AND ' + QUOTENAME(@ColumnName) + ' <= ''' + REPLACE(@MaxVal, '''', '''''') + '''';
            ELSE
                @Clause = QUOTENAME(@ColumnName) + ' < ''' + REPLACE(@MinVal, '''', '''''') + ''' OR ' + QUOTENAME(@ColumnName) + ' > ''' + REPLACE(@MaxVal, '''', '''''') + '''';
        END
        -- 解析通配符条件（含'*'）
        ELSE
        BEGIN
            DECLARE @Pattern NVARCHAR(MAX) = REPLACE(@Condition, '*', '%');
            IF @IsNegative = 0
                @Clause = QUOTENAME(@ColumnName) + ' LIKE ''' + REPLACE(@Pattern, '''', '''''') + '''';
            ELSE
                @Clause = QUOTENAME(@ColumnName) + ' NOT LIKE ''' + REPLACE(@Pattern, '''', '''''') + '''';
        END

        -- 拼接条件子句
        IF @IsNegative = 0
        BEGIN
            IF @PositiveClauses <> '' SET @PositiveClauses += ' OR ';
            @PositiveClauses += '(' + @Clause + ')';
        END
        ELSE
        BEGIN
            IF @NegativeClauses <> '' SET @NegativeClauses += ' AND ';
            @NegativeClauses += '(' + @Clause + ')';
        END

        FETCH NEXT FROM cur INTO @Condition;
    END
    CLOSE cur;
    DEALLOCATE cur;

    -- 构建最终查询语句
    DECLARE @FinalSQL NVARCHAR(MAX) = 'SELECT * FROM ' + QUOTENAME(@TableName) + ' WHERE ';

    IF @PositiveClauses <> '' AND @NegativeClauses <> ''
        @FinalSQL += '(' + @PositiveClauses + ') AND (' + @NegativeClauses + ')';
    ELSE IF @PositiveClauses <> ''
        @FinalSQL += @PositiveClauses;
    ELSE IF @NegativeClauses <> ''
        @FinalSQL += @NegativeClauses;
    ELSE
        @FinalSQL += '1=1'; -- 无过滤条件时返回全部数据

    -- 执行动态SQL
    EXEC sp_executesql @FinalSQL;
END
GO

使用示例 #

-- 执行示例过滤条件
EXEC GetFilteredData @FilterID = 1;

关键说明 #

1. SQL注入防护：使用QUOTENAME处理表/列名，替换字符串中的单引号避免注入风险。
2. 性能优化：动态SQL允许查询优化器根据实际条件生成最优执行计划，比逐行判断的表值函数效率更高。
3. 灵活性：支持任意符合Dynamics AX规则的过滤条件，用户可直接修改UserFilters表中的FilterCondition字段实现动态更新。

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