I've got a procedure defined as following

SET TERM ^ ;

/* Stored procedures */

CREATE PROCEDURE "GetTenantNames"
(
"LKey" INTEGER
)
RETURNS
(
"Names" VARCHAR(128) CHARACTER SET WIN1251
)
AS
BEGIN EXIT; END ^


ALTER PROCEDURE "GetTenantNames"
(
"LKey" INTEGER
)
RETURNS
(
"Names" VARCHAR(128) CHARACTER SET WIN1251
)
AS
Declare Variable name VarChar(32);
Begin
"Names" = Null;
For
Select "FirstName" || ' ' || "LastName"
From "TenantActivity"
Where "LeaseKey"=:"LKey"
Into name
Do
Begin
If ("Names" Is Not Null) Then
Begin
"Names" = "Names" || ', ';
"Names" = "Names" || name;
End
Else
"Names" = name;
End
End
^

SET TERM ; ^
COMMIT WORK;

It works just fine save for the fact that its kinda slow when called by the following procedure:

SET TERM ^ ;

/* Stored procedures */

CREATE PROCEDURE "AllTenantNames"
RETURNS
(
"LeaseKey" INTEGER,
"Names" VARCHAR(128) CHARACTER SET WIN1251
)
AS
BEGIN EXIT; END ^


ALTER PROCEDURE "AllTenantNames"
RETURNS
(
"LeaseKey" INTEGER,
"Names" VARCHAR(128) CHARACTER SET WIN1251
)
AS
Declare Variable name VarChar(32);
Begin
For
Select L."LeaseKey"
From "Leases" L
Into "LeaseKey"
Do
Begin
Execute Procedure "GetTenantNames" ("LeaseKey") Returning_Values ("Names");
Suspend;
End
End
^

SET TERM ; ^
COMMIT WORK;

The idea is to get all the tenant names associated with a particular lease and return them as a comma delimited list per lease. Is there a better way of doing this that won't take 2-3 seconds to calculate over 600 tenant entries and 400 lease entries?

R.


[Non-text portions of this message have been removed]