Hi,
I have been trying to implement following proc using WITH RECURSIVE cluase, as I need to get rid-off the cursur. I am not getting the desired result with my understanding of WITH RECURSIVE. Please help me with proper TD sql for this proc implementation or give me an example of how to use WITH RECURSIVE to implement while loop, if possible with an example or my understanding.

procedure:

REPLACE PROCEDURE sandbox.inventory_ref_load ()

BEGIN

DECLARE v_inventory_code_id DECIMAL(15,0);

DECLARE v_inventory_code_id1 DECIMAL(15,0);

DECLARE v_parent_inventory_code_id DECIMAL(15,0);

DECLARE v_parent_inventory_code_id1 DECIMAL(15,0);

DECLARE p_inventory_code_id DECIMAL(15,0);

DECLARE v_priority DECIMAL(5,0);

DECLARE p_priority DECIMAL(5,0);

DECLARE u_priority DECIMAL(5,0);

DECLARE v_stationid DECIMAL(15,0);

DECLARE v_loop INTEGER;

FOR rowval AS rec CURSOR FOR SELECT * FROM inventory_code

DO

SET v_loop = 1;

SET v_inventory_code_id=rowval.inventory_code_id;

SET v_parent_inventory_code_id1=rowval.parent_inventory_co de_id;

SET v_priority=rowval.priority;

SET v_stationid=rowval.stationid;

IF v_priority > 30 THEN

WHILE v_loop <> 0

DO

SELECT inventory_code_id,parent_inventory_code_id,priority INTO v_inventory_code_id1,p_inventory_code_id,p_priority FROM inventory_code

WHERE inventory_code_id=:v_inventory_code_id;

IF p_priority <=30 OR p_inventory_code_id IS NULL THEN

SET u_priority=p_priority;

SET v_loop =0;

ELSE

SET v_inventory_code_id =p_inventory_code_id;

END IF;

END WHILE;

END IF;

IF v_loop = 0 AND p_inventory_code_id IS NOT NULL THEN

INSERT INTO inventory_code_cross_ref VALUES(:rowval.inventory_code_id,:rowval.priority,: rowval.parent_inventory_code_id,: v_inventory_code_id1,:u_priority,:v_stationid);

END IF;

END FOR;

END;

Thanks
Chandrashekar