How get data and return as string in function in mysql?

Question

I have a table and when i make select i have results with two records. So now is ok. This result is INT. I want create function when i get each one of this value and add as string and return as string. Now i have result. What is wrong ?

pno

123 873

and after function i want

pno

123, 873, etc etc

BEGIN
        DECLARE done INT DEFAULT FALSE;
        declare i_myfield int;
        declare abc int;
        declare mycur cursor for SELECT pno FROM ptable WHERE no = no_id;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        set abc = 0;
        open mycur;

        read_loop: loop
            fetch mycur into i_myfield;
            IF done THEN
                LEAVE read_loop;
            END IF;
            SET abc = abc + CONVERT(i_myfield, CHAR(50));

        END loop;
    close mycur;
    return pono;
END

JNevill · Accepted Answer · 2017-10-03 16:46:50Z

1

Consider having your database do this for you (instead of looping through your cursor, which can be expensive):

 SELECT GROUP_CONCAT(pno) FROM ptable WHERE no = no_id;

That replicates all of this code in one simple statement.

answered Oct 3, 2017 at 16:46

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

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

1 Comment

JNevill Over a year ago

No worries. We've all reinvented the wheel once or twice ;)

Collectives™ on Stack Overflow

How get data and return as string in function in mysql?

pno

pno

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

pno

pno

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related