MySQL User Defined Functions store query results

Question

Goal: I'm trying to store the tables results of a query as a variable in a function. I'd like to be able to do more things in this procedure referencing these tables.

I've got a scaffold function below:

DELIMITER $$
CREATE FUNCTION getRatio(treatmentA INT, treatmentB INT)
RETURNS FLOAT
BEGIN
    DECLARE answer FLOAT;
    DECLARE countA, countB, countABIntersection;

    # Store patients of treatment A
    SELECT DISTINCT patient
    INTO **Variable**
    FROM Treatment
    WHERE TreatmentID = treatmentA;

    # Store patients of treatment B
    SELECT DISTINCT patient
    INTO **Variable**
    FROM Treatment
    WHERE TreatmentID = treatmentB;

    # Get Intersection of treatment B

    Set answer = count(TreatmentA) * count(TreatmentB) * count(IntersectionTreatmentAB)

    RETURN answer;
END $$
DELIMITER ;

If anyone can help, it would be greatly appreciated. Thanks in advance.

You can store a single value in above mentioned variables in Select statement. not sure how many values your select statement is returning. — Deepak Rai
– Deepak Rai, Commented Feb 24, 2014 at 7:02

avisheks · Accepted Answer · 2014-02-24 07:38:47Z

3

You only can store or return a value from a function which MySQL have datatypes.

In short if you want to store a result set to variable you have to have a table datatype, unfortunately MySQL doesn't have such.

Solution: What you can do is, you can create temporary tables with respective columns for each result set to store(or may be in the same temp table in your case).

Do all operations on the temp table and store the result to a variable(FLOAT in you case), return the variable.

Let me know if that helps :)

-Thanks

answered Feb 24, 2014 at 7:38

avisheks

1,1801 gold badge11 silver badges28 bronze badges

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

1 Comment

SalicBlu3 Over a year ago

Thanks! I didn't know that the table datatype was not available from MySQL.

Collectives™ on Stack Overflow

MySQL User Defined Functions store query results

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related