I am trying to write a stored procedure to concatenate multiple rows of text together to return it as a single string. For example:
CREATE TABLE TEST (
ID INTEGER,
SEQ INTEGER,
TEXT VARCHAR(255));
COMMIT;
INSERT INTO TEST (ID, SEQ, TEXT) VALUES (1, 1, "LINE 1");
INSERT INTO TEST (ID, SEQ, TEXT) VALUES (1, 2, "LINE 2");
INSERT INTO TEST (ID, SEQ, TEXT) VALUES (1, 3, "LINE 3");
COMMIT;
SET TERM !!;
CREATE PROCEDURE concat_names (iID INTEGER)
RETURNS (CONCAT VARCHAR(2000))
AS
DECLARE VARIABLE name VARCHAR(255);
BEGIN
CONCAT = '';
FOR SELECT TEXT FROM TEST where id=:iID INTO :name
DO BEGIN
CONCAT = CONCAT || name;
END
END!!
SET TERM ;!!
commit;
However when I run:
select concat from concat_names(1);
It always returns zero rows.
Any ideas?
You forget for SUSPEND. Your proc should look like this:
SET TERM !!;
CREATE PROCEDURE concat_names (iID INTEGER)
RETURNS (CONCAT VARCHAR(2000))
AS
DECLARE VARIABLE name VARCHAR(255);
BEGIN
CONCAT = '';
FOR SELECT TEXT FROM TEST where id=:iID INTO :name
DO BEGIN
CONCAT = CONCAT || name;
END
SUSPEND;
END!!
SET TERM ;!!
You can achieve the same result without stored proc. Use LIST aggregate function:
SELECT LIST(text, '') FROM TEST where id=:iID
Second parameter of LIST is a delimiter. If you call LIST with only field name, then comma ',' will be used to separate values.