Passing on a bit of FYI... I found calling a stored procedure from
another stored procedure isn't as fast as you would think it would be.
Apparently there is some sort of overhead.

Jason

Ranando King wrote:

> 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]
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>