Using recursive CTE in stored proc?

[Roger Barney]

I have a need to run a recursive CTE within a stored proc, but I can't get it past this: SQL0104N An unexpected token "with" was found following "SET count=count+1; ". Expected tokens may include: "". LINE NUMBER=26.

My google-fu showed a couple of similar topics, but none with resolution.

The query functions as expected outside of the stored proc, so I'm hoping that there's some syntactic sugar I'm missing that'll let this work. Similarly, the proc compiles and works without the query.

Here's a contrived example:

--setup
create table tree (id integer, name varchar(50), parent_id integer);
insert into tree values (1, 'Alice', null);
insert into tree values (2, 'Bob', 1);
insert into tree values (3, 'Charlie', 2);

-- the proc
create or replace procedure testme() RESULT SETS 1 LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5);
DECLARE SQLCODE integer default 0;
DECLARE count INTEGER;
DECLARE sum INTEGER;
DECLARE total INTEGER;
DECLARE id INTEGER;
DECLARE curs CURSOR WITH RETURN FOR 
select count,sum from sysibm.sysdummy1;
 
DECLARE hiercurs CURSOR FOR 
select id from tree order by id;
SET bomQuery='';
PREPARE stmt FROM bomQuery;
SET count = 0;
SET sum = 0;
set total = 0;
OPEN hiercurs;
FETCH hiercurs INTO id;
WHILE (SQLCODE <> 100) DO
SET count=count+1;
 
with org (level,id,name,parent_id) as
(select 1 as level,root.id,root.name,root.parent_id from tree root where root.id=id
union all
select level+1,employee.id,employee.name,employee.parent_ id from org boss, tree employee 
where level < 5 and employee.parent_id=boss.id)
select count(1) into sum from org;
 
SET total=total+sum;
FETCH hiercurs INTO id;
END WHILE;
CLOSE hiercurs;
OPEN curs;
END

Did I mention this is a contrived example? I'm not looking for optimizations--just need help getting the CTE in the while loop to be accepted by DB2 9.5+

Thanks in advance,
-r

[Roger Barney]

the cte in db2 doesn't seem to recognize the scalar result of the query, and so it won't let the select into work (not a problem on Oracle or SQLServer)...solution is to open a cursor and FETCH INTO (instead of SELECT INTO) instead.