set term !!;

create or alter procedure connect_by_prior (
Table_Name varchar(32),
Pk varchar(32), /* parent rows name /connect by prior/ */
Fk varchar(32), /* child rows name /connect by prior/ */
Start_With varchar(256), /* Start condition, for example: 'ref_to_id=0' */
Order_by varchar(256), /* Fields names, comma separated; or <NULL> */
Level_In decimal(18,0) /* In first call binding <NULL> */
/* Example: select * from connect_by_prior('project','id','ref_to_id','ref_to_id=0',null,null); */
)
returns (
Id decimal(18,0),
Tree_Level decimal(18,0))
as
declare d_start_with varchar(256);
declare i decimal(18,0);
begin

if (Level_In is null) then
Level_In = 0;
Tree_Level = Level_in;

For execute statement
'select '||pk||' from '||table_name||' where '||start_with||
case
when order_by is null then ''
else ' order by '||order_by
end || ''
into :id
do begin
suspend;
d_start_with = fk || '=' || id;
i = Tree_Level;
For select id, tree_level
from connect_by_prior(:table_name, :pk, :fk, :d_start_with, :order_by, (:tree_level+1))
into :id, :tree_level
do suspend;
tree_level = i;
end
end !!

set term ; !!