Firebird 2.1 stored procedure to concatenate text on multiple rows

I am trying to write a stored procedure to concatenate multiple rows of text together to return it as a single开发者_JAVA技巧 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.

In the case the field TEST can ben null and you don't want to set to null the whole result it is useful to use:

 CONCAT = CONCAT || coalesce(name,'');

instead of

CONCAT = CONCAT || name;

Without utilizing a Stored Proc and using version Firebird 2.5, the LIST aggregation function will return "Comma-separated string concatenation of non-NULL values in the column"*. Using the aforementioned TEST table, the SQL

SELECT LIST(TEXT)
    FROM TEST

returns

LINE 1,LINE 2,LINE 3

This may be of some interest.

*Taken from the Firebird reference page here