Thank you Alex, It works now. Perfect!!
How do you control the infinite loops in Sub-Partners?
Before commiting new row to table, I need to check the
P_Code.Parent_code.Parent_code.... <> P_Code /*must be*/
Do you have any suggestion?
Regards,

----- Original Message -----
From: "Alex Taylor" <basswar@...>
Newsgroups: egroups.ib-support
To: <firebird-support@yahoogroups.com>
Sent: Wednesday, January 07, 2004 2:50 PM
Subject: Re: [firebird-support] SP question BOMVIEW recursive SP call


> Hey!
>
> Look at this code snippet. Maybe it will help You. It works for me. This
> procedure pass back all the sub-partners of a given partner.
> It is recursive of course and works fine.
>
> CREATE PROCEDURE GET_SUB_PARTNERS(
> PARENT_CODE SMALLINT)
> RETURNS (
> P_CODE SMALLINT,
> NAME CHAR(50))
> AS
> DECLARE VARIABLE HAS_CHILDREN SMALLINT;
> BEGIN
> FOR SELECT P_CODE,NAME FROM PARTNER WHERE PARENT_CODE=:PARENT_CODE
> INTO :P_CODE, :NAME DO
> BEGIN
> SELECT COUNT(*) FROM PARTNERS WHERE PARENT_CODE=:P_CODE INTO
> :HAS_CHILDREN;
>
> IF (HAS_CHILDREN<>0) THEN
> BEGIN
> SELECT P_CODE, NAME FROM GET_SUB_PARTNERS(:P_CODE) INTO
> :P_CODE, :NAME;
> END
>
> SUSPEND;
> END
> END
>
> Regards: Alex Taylor
>
>
>
> Yahoo! Groups Links
>
> To visit your group on the web, go to:
> http://groups.yahoo.com/group/firebird-support/
>
> To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
> Your use of Yahoo! Groups is subject to:
> http://docs.yahoo.com/info/terms/
>
>