Thanks, I'll try that.

BTW, I tried to simplify the procedure a little and left the
"SUBNET_PATH_RES" in there. Sorry about that :o(

--- In firebird-support@yahoogroups.com, "Martijn Tonies"
<m.tonies@u...> wrote:

> Hi,
>
> > Can anybody tell me what is wrong with this Stored Procedure?
> >
> > It is supposed to return a path of the hierarchical data in the

database.

> >
> > ---------------------------------------------------
> >
> > SET TERM ^;
> >
> > CREATE PROCEDURE GET_ITEM_PATH(A_ITEM VARCHAR(39))
> > RETURNS (ITEM_PATH VARCHAR(4000))
> > AS
> > DECLARE A_ITEM_PARENT VARCHAR(29);
> > BEGIN
> > IF (NOT (A_ITEM='ROOT')) THEN
> > BEGIN
> > SELECT ITEM_PARENT
> > FROM ITEMS
> > WHERE ITEM=:A_ITEM
> > INTO :A_ITEM_PARENT;
> >
> > SUBNET_PATH_RES = GET_ITEM_PATH(A_ITEM_PARENT)||'/'||ITEM_PATH;
> > END
> > END
> >
> > ----------------------------------------------------
> >
> > When I try to add the porocedure I get the error "Function unknown
> > 'GET_ITEM_PATH'. Are recursive procedure calls not allowed in Firebird
> > or am I doing something wrong?
>
> Recursive procedure are allowed, but you need to call it as
> a procedure, not a function. Even though this procedure
> only returns one parameter, it's a parameter, not a result.
>
> Think of it as a Pascal procedure:
> procedure GET_ITEM_PATH(const A_ITEM, var ITEM_PATH )
>
> Not like this:
> function GET_ITEM_PATH(const A_ITEM): string;
>
> So, call it like:
> execute procedure GET_ITEM_PATH(A_ITEM_PARENT) returning_values
> :subnet_path_res;
> subnet_path_res = subnet_path_res || '/' || item_path;
>
>
> With regards,
>
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL &

MS SQL

> Server.
> Upscene Productions
> http://www.upscene.com