MySQL multiple result rows in stored function

Question

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!

Do you really, really need to do the calculation in the database? Maybe try to rethink the problem from a higher level? Given some more context, we might be able to come up with a better answer. — Wim Deblauwe
– Wim Deblauwe, Commented Nov 16, 2012 at 20:39

John Woo · Accepted Answer · 2012-11-16 12:45:04Z

1

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.

answered Nov 16, 2012 at 12:45

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gert789 Over a year ago

Your solution is possible but the problem is that calculatePrice get's called twice. In this example it's tiny and fast, but in the real application it's very large, calling it twice would generate a serious delay... Writing two different functions isn't a great answer for the same reason, and it's hardly maintainable.

Sashi Kant · Accepted Answer · 2012-11-16 12:46:06Z

1

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

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

answered Nov 16, 2012 at 12:46

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

Collectives™ on Stack Overflow

MySQL multiple result rows in stored function

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related