MySQL存储过程执行报错ERROR 1172：结果集返回多行求助

Hey there! Let's break down why you're hitting that ERROR 1172 and fix your stored procedure step by step.

What's causing the error? #

The ERROR 1172 (42000): Result consisted of more than one row happens because your SELECT ... INTO statements are trying to assign multiple rows of data to a single variable (discountPer). MySQL's INTO clause only works with single-row results—when your query returns more than one match, it throws this error.

Looking at your code, two main issues are triggering this:

1. Missing join condition: Your TBL_PRODUCT_DISCOUNT join only filters by productId, but doesn't link to the discount tables (TBL_DISCOUNT/TBL_DISCOUNT_DETAIL). This creates a Cartesian product, returning way more rows than intended.
2. Multiple valid discounts: Even with correct joins, a product might match multiple active discount records, leading to multiple rows in the result set.

Fixes to resolve the error #

Let's address both issues with two practical solutions:

Option 1: Get a single discount (e.g., highest applicable discount)

If you only need to apply the best (highest) discount for each scheme type, use an aggregate function like MAX() and fix the join conditions to avoid Cartesian products. Here's the updated stored procedure:

delimiter // 
CREATE PROCEDURE calculateNetItemAmount(IN productId INT,IN quantity INT, OUT netItemAmount DOUBLE) 
BEGIN 
    DECLARE discountPer DOUBLE DEFAULT 0; 
    
    -- Fetch product price and calculate initial total
    SELECT `SellingUnitPrice` INTO netItemAmount FROM `TBL_PRODUCT_MASTER` WHERE `Id` = productId; 
    SET netItemAmount = quantity * netItemAmount ; 
    
    -- Handle Quantity Discount (get highest discount, fix join logic)
    SELECT MAX(discDetail.`DiscountPercentage`) INTO discountPer 
    FROM `TBL_DISCOUNT_DETAIL` AS discDetail 
    JOIN `TBL_DISCOUNT` AS disc ON discDetail.DiscountId = disc.Id
    JOIN TBL_PRODUCT_DISCOUNT AS prodDisc ON prodDisc.DiscountId = disc.Id AND prodDisc.productId = productId
    WHERE disc.`DiscountStartDate` < NOW() 
      AND disc.`DiscountEndDate` > NOW() 
      AND disc.`IsEnabled` = 1 
      AND disc.`SchemeType` = 'Quantity Discount'
      AND prodDisc.`IsEnabled` = 1; 
      
    IF discountPer IS NOT NULL THEN 
        SET netItemAmount = netItemAmount * (1 - discountPer * 0.01);
    END IF; 
    
    -- Handle Volume Discount (same fix for joins and single-row result)
    SELECT MAX(discDetail.`DiscountPercentage`) INTO discountPer 
    FROM `TBL_DISCOUNT_DETAIL` AS discDetail 
    JOIN `TBL_DISCOUNT` AS disc ON discDetail.DiscountId = disc.Id
    JOIN TBL_PRODUCT_DISCOUNT AS prodDisc ON prodDisc.DiscountId = disc.Id AND prodDisc.productId = productId
    WHERE disc.`DiscountStartDate` < NOW() 
      AND disc.`DiscountEndDate` > NOW() 
      AND disc.`IsEnabled` = 1 
      AND disc.`SchemeType` = 'Volume Discount'
      AND prodDisc.`IsEnabled` = 1; 
      
    IF discountPer IS NOT NULL THEN 
        SET netItemAmount = netItemAmount * (1 - discountPer * 0.01);
    END IF; 
END// 
delimiter ;

Option 2: Apply multiple discounts (if needed)

If your business logic requires applying all matching discounts (e.g., stacking multiple schemes), use a cursor to iterate over the multiple rows of discount data. Here's how to adjust the quantity discount section as an example:

delimiter // 
CREATE PROCEDURE calculateNetItemAmount(IN productId INT,IN quantity INT, OUT netItemAmount DOUBLE) 
BEGIN 
    DECLARE discountPer DOUBLE DEFAULT 0; 
    DECLARE done BOOLEAN DEFAULT FALSE; -- Cursor flag
    
    -- Fetch product price and calculate initial total
    SELECT `SellingUnitPrice` INTO netItemAmount FROM `TBL_PRODUCT_MASTER` WHERE `Id` = productId; 
    SET netItemAmount = quantity * netItemAmount ; 
    
    -- Cursor for Quantity Discounts
    DECLARE cur_quantity_disc CURSOR FOR
        SELECT discDetail.`DiscountPercentage` 
        FROM `TBL_DISCOUNT_DETAIL` AS discDetail 
        JOIN `TBL_DISCOUNT` AS disc ON discDetail.DiscountId = disc.Id
        JOIN TBL_PRODUCT_DISCOUNT AS prodDisc ON prodDisc.DiscountId = disc.Id AND prodDisc.productId = productId
        WHERE disc.`DiscountStartDate` < NOW() 
          AND disc.`DiscountEndDate` > NOW() 
          AND disc.`IsEnabled` = 1 
          AND disc.`SchemeType` = 'Quantity Discount'
          AND prodDisc.`IsEnabled` = 1;
          
    -- Handle cursor end
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- Apply all quantity discounts
    OPEN cur_quantity_disc;
    read_disc_loop: LOOP
        FETCH cur_quantity_disc INTO discountPer;
        IF done THEN
            LEAVE read_disc_loop;
        END IF;
        SET netItemAmount = netItemAmount * (1 - discountPer * 0.01);
    END LOOP;
    CLOSE cur_quantity_disc;
    
    -- Repeat the cursor logic for Volume Discounts here...
    
END// 
delimiter ;

Key Takeaways #

• Always verify your join conditions to avoid unintended Cartesian products (this was the biggest issue in your original code).
• Use MAX()/MIN() or LIMIT 1 if you only need one discount value.
• Use cursors if you need to process multiple discount records for a single product.

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