Sounds like a homework question to me, but maybe not.
Here are some tips. I think you will need to use a cursor to do
something like this. Read about them in the MySQL manual.
Here is an example from the manual: -
CREATE PROCEDURE curdemo()
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF NOT done THEN
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
INSERT INTO test.t3 VALUES (a,c);
UNTIL done END REPEAT;
The example is using two cursors where you need only one. Your procedure
will need two parameters unlike this one which has none. Instead of the
INSERTs inside the repeat loop you will be concatenating strings as you
process each row.
Any other details you need should be in the manual.
Carl Zeiss Vision
19 Cooroora Crescent
Lonsdale SA 5160
Phone: +61 (0) 8 8392 8366
Fax: +61 (0) 8 8392 8400
This email, including any attachments, is intended for the sole use of
the intended recipient(s) and may contain
information that is confidential, privileged and/or the sender's work.
Any review or distribution by any other person is
prohibited. If you are not an intended recipient, please immediately
contact the sender and delete all copies.
From: Jorge Bastos [mailto:mysql.jorge@stripped]
Sent: Saturday, 28 June 2008 10:28 PM
Subject: Offtopic help
I wounder if anyone can give me a hand.
I want to create a view or function/procedure that returns a string, and
inside that function I have to pass two arguments, and do a SQL
statement like this:
Select * from tbl where field1=art1 and field2=arg2;
Result=concat(field3 ,';') <= for each record found I need to
concatenate the result, and return it on the end.
How can this be possible?