CODEWITHSUNDEEP
mysqlstored-procedures
Cups
Cups

Reputation: 6896

How to loop through results in an sproc?

I'm learning about stored procedures in mysql (5.5), and have hit a bit of a mental block here about what can be done using sprocs.

The base data looks like this:

select * from fruit;

name | variety | price | quantity
---------------------------------
Pear Comice - 15 -  2
Pear Barlett - 20  - 3
Pear Anjou  - 20 - 3
Apple Red - 10 - 7 
etc

How do I get the combined monetary value of ALL types of a fruit, say, all Pear types?

I got as far as making this sproc which will get the value of a single variety of a fruit.

DROP PROCEDURE IF EXISTS getStockValue;

DELIMITER // CREATE PROCEDURE `getStockValue`(
IN variety varchar(20),
IN vat BOOLEAN,
OUT tot DECIMAL(8,2)
)

BEGIN
DECLARE nett_value INT;
SELECT (quantity*price) INTO nett_value from fruit where variety = variety;

IF vat = 1 THEN
SELECT (nett_value*20/100)+(nett_value) INTO tot;
ELSE
SELECT nett_value INTO tot;
END IF;
SELECT tot;

END;// DELIMITER ;

CALL getStockValue('Comice',1,@tot);

So from my base data you see that without VAT it should come back with the total 150, and with VAT 180.

Do I have another sproc which loops through a result set somehow? What is the best way to tackle this so that this computation stays on the database server? Is this where a cursor would be used?

I've read an awful lot about when to/not to use sprocs, but I have an interview with a company that have warned me they rely heavily on them already.

EDIT - in order to clarify my overall question.

How do I get from where I am:

CALL getStockValue('Comice',1,@tot); 
// gives 36

(in hindsight should be renamed getStockValueByVariety())

To where I want to be:

CALL getStockValueByName('Pear',1,@tot); 
// gives 180 - because it gets ALL Pear types, not just the variety Comice

FINALLY - twigged, I was missing a GROUP BY ...

SELECT SUM(price*quantity) as tot 
FROM fruit 
WHERE name = 'Pear' 
GROUP BY name;

Upvotes: 2

Views: 78

Answers (1)

swasheck
swasheck

Reputation: 4693

use a CASE statement and just return the value from the stored procedure.

SELECT 
    CASE vat
        WHEN 1 THEN (((quantity*price)*20/100) + (quantity * price)) 
        ELSE (quantity*price)
    END AS nett_value
FROM fruit;

If you want the sum total of all of a particular variety, then SUM it

SELECT 
    SUM(CASE vat
        WHEN 1 THEN (((quantity*price)*20/100) + (quantity * price)) 
        ELSE (quantity*price)
    END) AS tot
FROM fruit
WHERE variety = @variety 
GROUP BY
    name

Upvotes: 5

Related Questions