SET Command Appears to be Ignored in MySQL Procedure-CodePudding

I finally got this function to work correctly until I tried to implement in a production state. When I run this procedure and call, I am only getting a value for the LowerLimit output and a NULL for the UpperLimit output. I added a SELECT in the procedure that shows the results for both the internal variables @lowlim and @upplim are being computed, but it appears that only the set for the LowerLimit variable is working. I've been banging on this for hours and can't figure it out so far. Can anyone see anything obvious I'm missing here? Here is where the problem is occurring:

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

Here is the full procedure.

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;

CodePudding user response：

You need to declare OUT for each parameter:

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

https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html says:

Each parameter is an IN parameter by default. To specify otherwise for a parameter, use the keyword OUT or INOUT before the parameter name.