MySQL存储过程SET赋值失效 UpperLimit输出参数返回NULL

存储过程部署至生产环境调用时，OUT类型参数仅LowerLimit可正常返回计算值，UpperLimit始终返回NULL。存储过程内部新增校验查询确认会话变量@lowlim、@upplim均已完成正确计算，仅UpperLimit的赋值逻辑未对外生效，长时间排查未定位根因。

异常位置代码片段 #

SET LowerLimit = @lowlim;
SET UpperLimit = @upplim;   
SELECT @lowlim, @upplim;

完整存储过程与调用代码 #

DELIMITER $$
DROP PROCEDURE if exists p_GetOutlierLimits;
CREATE DEFINER=`user`@`%` PROCEDURE p_GetOutlierLimits(
    IN KPI VARCHAR(255), TableName VARCHAR(100),
    OUT LowerLimit decimal(16,6), UpperLimit decimal(16,6)
    )
BEGIN
    SET @lowlim = 0;
    SET @upplim = 0;
    SET @SQLExec = CONCAT("
    with orderedList AS (
    SELECT
        ",KPI,",
        ROW_NUMBER() OVER (ORDER BY ",KPI,") AS row_n
    FROM ",TableName,"
    ),
    
    quartile_breaks AS (
    SELECT
        ",KPI,",
        (
        SELECT ",KPI," AS quartile_break
        FROM orderedList
        WHERE row_n = FLOOR((SELECT COUNT(*) FROM ",TableName,")*0.75)
        ) AS q_three_lower,
        (
        SELECT ",KPI," AS quartile_break
        FROM orderedList
        WHERE row_n = FLOOR((SELECT COUNT(*) FROM ",TableName,")*0.75) + 1
        ) AS q_three_upper,
        (
        SELECT ",KPI," AS quartile_break
        FROM orderedList
        WHERE row_n = FLOOR((SELECT COUNT(*) FROM ",TableName,")*0.25)
        ) AS q_one_lower,
        (
        SELECT ",KPI," AS quartile_break
        FROM orderedList
        WHERE row_n = FLOOR((SELECT COUNT(*) FROM ",TableName,")*0.25) + 1
        ) AS q_one_upper
        FROM orderedList
        ),
    
    iqr AS (
    SELECT
        ",KPI,",
        (
        (SELECT MAX(q_three_lower)
            FROM quartile_breaks) +
        (SELECT MAX(q_three_upper)
            FROM quartile_breaks)
        )/2 AS q_three,
        (
        (SELECT MAX(q_one_lower)
            FROM quartile_breaks) +
        (SELECT MAX(q_one_upper)
            FROM quartile_breaks)
        )/2 AS q_one,
        1.5 * ((
        (SELECT MAX(q_three_lower)
            FROM quartile_breaks) +
        (SELECT MAX(q_three_upper)
            FROM quartile_breaks)
        )/2 - (
        (SELECT MAX(q_one_lower)
            FROM quartile_breaks) +
        (SELECT MAX(q_one_upper)
            FROM quartile_breaks)
        )/2) AS outlier_range
    FROM quartile_breaks
    )
    
    SELECT MAX(q_one) OVER () - MAX(outlier_range) OVER () AS lower_limit,
        MAX(q_three) OVER () + MAX(outlier_range) OVER () AS upper_limit
    INTO @lowlim, @upplim
    FROM iqr
    LIMIT 1;");
    PREPARE stmt FROM @SQLExec;
    EXECUTE stmt;

    SET LowerLimit = @lowlim;
    SET UpperLimit = @upplim;   
    SELECT @lowlim, @upplim;
END$$
DELIMITER ;

CALL p_GetOutlierLimits('576_VMC_Sol_Savings_Pct','vmctco',@LowerLimit, @UpperLimit);
SELECT @LowerLimit, @UpperLimit;

• 核心错误出在存储过程的参数定义段：MySQL存储过程的参数模式（IN/OUT/INOUT）仅对紧邻其后的单个参数生效，不会批量修饰逗号分隔的后续参数，未显式标注模式的参数会默认被识别为IN类型。
• 原参数定义中，仅LowerLimit前显式标注了OUT，同段的UpperLimit前未加OUT修饰，被MySQL默认解析为IN类型参数。IN类型参数仅会将调用时传入的值拷贝至存储过程内部，过程内部对该参数的任何修改都不会回传至外层调用方的会话变量。
• 调用存储过程前未对传入的@UpperLimit变量赋值，其初始值为NULL，因参数是IN模式，内部赋值无法回写，最终外层查询就只能得到NULL结果。
• 参数列表中的TableName前也未显式标注IN，只是因为它本身就是入参，默认IN模式符合预期，才没有触发异常，属于不规范写法。

调整存储过程参数定义，为每个参数显式标注对应的模式，修改后的参数定义段如下：

CREATE DEFINER=`user`@`%` PROCEDURE p_GetOutlierLimits(
    IN KPI VARCHAR(255), 
    IN TableName VARCHAR(100),
    OUT LowerLimit decimal(16,6), 
    OUT UpperLimit decimal(16,6)
)

修改后重新创建存储过程，再次调用即可正常获取两个OUT参数的返回值。

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