多价格范围搜索适配：动态SQL转静态SQL后的存储过程问题

针对你已经把动态SQL搜索转成静态、且已有订单号字符串解析为表值的基础，这里分享几个适配的方案来实现发票总额这类价格字段的多范围搜索：

方案1：复用表值参数+自定义范围解析逻辑 #

既然你已经有解析订单号字符串为表值的经验，可以直接复用类似思路：

步骤1：定义价格范围表值类型 #

先创建一个用于存储价格范围的表类型，方便在存储过程中接收或生成范围数据：

CREATE TYPE dbo.PriceRangeType AS TABLE (
    MinAmount DECIMAL(18,2) NULL, -- NULL代表无下限
    MaxAmount DECIMAL(18,2) NULL  -- NULL代表无上限
);

步骤2：实现范围字符串解析函数 #

写一个表值函数，把用户输入的范围字符串（比如"100-200,300-,500"）解析成上面的表类型数据：

CREATE FUNCTION dbo.ParsePriceRanges(@Input NVARCHAR(MAX))
RETURNS @Ranges TABLE (MinAmount DECIMAL(18,2) NULL, MaxAmount DECIMAL(18,2) NULL)
AS BEGIN
    DECLARE @SplitRange NVARCHAR(100);
    DECLARE RangeCursor CURSOR FOR
        SELECT Value FROM STRING_SPLIT(@Input, ',') WHERE Value <> '';
    
    OPEN RangeCursor;
    FETCH NEXT FROM RangeCursor INTO @SplitRange;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @DashPos INT = CHARINDEX('-', @SplitRange);
        IF @DashPos = 0
        BEGIN
            -- 单个数值，匹配等于该值的记录
            INSERT INTO @Ranges VALUES(CAST(@SplitRange AS DECIMAL(18,2)), CAST(@SplitRange AS DECIMAL(18,2)));
        END
        ELSE
        BEGIN
            DECLARE @MinStr NVARCHAR(50) = LEFT(@SplitRange, @DashPos - 1);
            DECLARE @MaxStr NVARCHAR(50) = RIGHT(@SplitRange, LEN(@SplitRange) - @DashPos);
            
            INSERT INTO @Ranges VALUES(
                CASE WHEN @MinStr = '' THEN NULL ELSE CAST(@MinStr AS DECIMAL(18,2)) END,
                CASE WHEN @MaxStr = '' THEN NULL ELSE CAST(@MaxStr AS DECIMAL(18,2)) END
            );
        END
        FETCH NEXT FROM RangeCursor INTO @SplitRange;
    END
    
    CLOSE RangeCursor;
    DEALLOCATE RangeCursor;
    RETURN;
END

步骤3：在存储过程中整合逻辑 #

在你的静态SQL存储过程里，调用这个函数解析用户输入的范围字符串，然后关联主订单表进行匹配：

CREATE PROCEDURE dbo.SearchOrders
    @OrderNumberInput NVARCHAR(MAX),
    @PriceRangeInput NVARCHAR(MAX)
AS BEGIN
    SET NOCOUNT ON;
    
    -- 解析订单号（复用你已有的逻辑）
    DECLARE @ParsedOrderNumbers TABLE (OrderNumber NVARCHAR(50));
    INSERT INTO @ParsedOrderNumbers
    SELECT Value FROM STRING_SPLIT(@OrderNumberInput, ',') WHERE Value <> '';
    
    -- 解析价格范围
    DECLARE @PriceRanges dbo.PriceRangeType;
    INSERT INTO @PriceRanges
    SELECT * FROM dbo.ParsePriceRanges(@PriceRangeInput);
    
    -- 关联查询
    SELECT o.*
    FROM Orders o
    -- 匹配订单号
    JOIN @ParsedOrderNumbers pon ON o.OrderNumber = pon.OrderNumber
    -- 匹配价格范围
    JOIN @PriceRanges pr 
        ON (pr.MinAmount IS NULL OR o.InvoiceTotal >= pr.MinAmount)
        AND (pr.MaxAmount IS NULL OR o.InvoiceTotal <= pr.MaxAmount);
END

方案2：利用JSON输入简化解析（SQL Server 2016+） #

如果用户可以调整输入格式为JSON，解析会更简洁且不易出错，比如用户输入'[{"Min":100,"Max":200},{"Min":300},{"Max":500}]'：

CREATE PROCEDURE dbo.SearchOrdersWithJson
    @OrderNumberInput NVARCHAR(MAX),
    @PriceRangeJson NVARCHAR(MAX)
AS BEGIN
    SET NOCOUNT ON;
    
    -- 解析订单号
    DECLARE @ParsedOrderNumbers TABLE (OrderNumber NVARCHAR(50));
    INSERT INTO @ParsedOrderNumbers
    SELECT Value FROM STRING_SPLIT(@OrderNumberInput, ',') WHERE Value <> '';
    
    -- 解析JSON格式的价格范围
    DECLARE @PriceRanges TABLE (MinAmount DECIMAL(18,2) NULL, MaxAmount DECIMAL(18,2) NULL);
    INSERT INTO @PriceRanges
    SELECT 
        ISNULL(Min, NULL) AS MinAmount,
        ISNULL(Max, NULL) AS MaxAmount
    FROM OPENJSON(@PriceRangeJson)
    WITH (
        Min DECIMAL(18,2) '$.Min',
        Max DECIMAL(18,2) '$.Max'
    );
    
    -- 关联查询（同方案1）
    SELECT o.*
    FROM Orders o
    JOIN @ParsedOrderNumbers pon ON o.OrderNumber = pon.OrderNumber
    JOIN @PriceRanges pr 
        ON (pr.MinAmount IS NULL OR o.InvoiceTotal >= pr.MinAmount)
        AND (pr.MaxAmount IS NULL OR o.InvoiceTotal <= pr.MaxAmount);
END

关键注意事项 #

• 边界处理：根据业务需求确认范围是否包含临界值（比如100-200是否包含等于100和200的记录），上面的例子用的是>=和<=，如果需要开区间可以调整为>和<。
• 性能优化：如果订单表数据量较大，确保InvoiceTotal字段有合适的索引；解析后的临时表/表值参数如果数据量多，可以考虑添加临时索引。
• 输入校验：在存储过程开头添加对输入字符串的校验，避免无效格式导致的转换错误，比如检查是否有非数字字符等。

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