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