CODEWITHSUNDEEP
mysqlstored-proceduresstored-functions
gert789
gert789

Reputation: 424

MySQL multiple result rows in stored function

I've written a MySQL function that calculates a price:

CREATE DEFINER=`[USER]`@`[HOST]` FUNCTION `calculatePrice`(id INT) 
RETURNS DECIMAL(8,2) DETERMINISTIC
BEGIN
    IF id=10 THEN 
        RETURN 899;
    ELSE
        RETURN 710;
    END IF;
END

This works how it should, but next to the price, I want to add a string containing the reason of the price.

That function should be used like this:

SELECT id, calculatePrice(id).price AS price, calculatePrice(id).reason AS reason FROM person;

Is this possible with MySQL and how does it work?

Thanks!

Upvotes: 0

Views: 1027

Answers (2)

Sashi Kant
Sashi Kant

Reputation: 13465

You can 1) Change the Return type to varchar 2) Return With Concatinating the price and description

using the function CONCAT(price,',',desc)

Upvotes: 1

John Woo
John Woo

Reputation: 263723

how about taking two parameters on it and the value will depend according to the second parameter, example

CREATE DEFINER=`[USER]`@`[HOST]` FUNCTION `calculatePrice`(id INT, isPrice INT) 
RETURNS DECIMAL(8,2) DETERMINISTIC
BEGIN
    IF isPrice = 1 THEN -- for price
        IF id = 10 THEN 
            RETURN 899;
        ELSE
            RETURN 710;
        END IF;
    ELSE                -- for reason
        IF id = 10 THEN 
            RETURN 100;
        ELSE
            RETURN 200;
        END IF;
    END IF
END

and call it like

SELECT  id, 
        calculatePrice(id, 1) AS price, 
        calculatePrice(id, 0).reason AS reason 
FROM person;

but if you don't like the idea, just make two separate function that suit your needs.

Upvotes: 1

Related Questions