CODEWITHSUNDEEP
mysqlstored-procedures
Shawn Benson
Shawn Benson

Reputation: 137

Calculations within MySQL Stored Procedures

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.

Upvotes: 2

Views: 1222

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28844

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 ;

Upvotes: 2

Related Questions