Cfrgtkky

I am creating a stored procedure that, when you input a class for an item, it gives you the total value. Because of this, I am having to add a calculation to my query within the SP. I keep getting an error message that reads: #1172 - Result consisted of more than one row. My guess is that it's because it's pulling from two places to get the product. Here is my code for the SP:

DELIMITER //

CREATE PROCEDURE totalValue (IN whichClass varchar(5),

                             OUT totalval int)

    BEGIN

        SELECT (price * numInStock) into totalval

        FROM hsitems

        WHERE itemClass = whichClass;

    END // 

DELIMITER ;

Now, when I go to input anything and call the procedure, I am getting the error message. Here is how I was attempting to call:

CALL  totalValue('HW',@totalval); 

SELECT @totalval;

Do I need to define the calculation AS something before putting it into totalval? I attempted that, and it still gave me an error message.

You basically have multiple rows for itemClass = 'HW' in the hsitems table. SELECT .. INTO documentation specifically states:

The query should return a single row. If the query returns no rows, a
warning with error code 1329 occurs (No data), and the variable values
remain unchanged. If the query returns multiple rows, error 1172
occurs (Result consisted of more than one row). If it is possible that
the statement may retrieve multiple rows, you can use LIMIT 1 to limit
the result set to a single row.

However, based on your variable name, I feel that you are trying to SUM(price*numinstock) instead to get the total value. This will also ensure implicit aggregation (GROUP BY) into a single row.

Also, by declaring OUT totalval INT, your output will be converted to int, and you will lose values after decimal. So I have changed it to OUT totalval DECIMAL(10,2). When dealing with currency numbers, it is preferred to use DECIMAL instead of floating point types like FLOAT and DOUBLE.

DELIMITER //

CREATE PROCEDURE totalValue (IN whichClass varchar(5),

                             OUT totalval DECIMAL(10,2))

    BEGIN

        SELECT SUM(price * numInStock) into totalval  -- changed to SUM()

        FROM hsitems

        WHERE itemClass = whichClass;

    END // 

DELIMITER ;

If you want to format the sum value upto two decimal places (like currency), we can utilize Format() function. Also, note that output will be String format now.

DELIMITER //

CREATE PROCEDURE totalValue (IN whichClass varchar(5),

                             OUT totalval VARCHAR(32))

    BEGIN

        SELECT FORMAT(SUM(price * numInStock), 2) into totalval 

        FROM hsitems

        WHERE itemClass = whichClass;

    END // 

DELIMITER ;