The following CREATE PROCEDURE definition specifies recursion.
CREATE PROCEDURE rqspl( OUT rowcnt INTEGER) BEGIN DECLARE target1 INTEGER DEFAULT 0; DECLARE target2 INTEGER DEFAULT 0; DECLARE target3 INTEGER DEFAULT 0; DECLARE finalcount INTEGER DEFAULT 0; DECLARE pscursor SCROLL CURSOR FOR WITH RECURSIVE RQ(x,y,depth) AS ( SELECT a1, b1, 0 FROM t1 UNION ALL SELECT a1, y, depth+1 FROM RQ,t1 WHERE y = a1 AND depth < 10 ) SELECT * FROM RQ; OPEN pscursor; FETCH pscursor INTO target1, target2, target3; -- if there is no row from the cursor, -- SQLCODE=7632, SQLSTATE=02000 WHILE (SQLCODE <> 7632) DO FETCH pscursor INTO target1, target2, target3; SET finalcount = finalcount+1; END WHILE; CLOSE pscursor; SET rowcnt = finalcount; END;