why to use with return for in cursor
while using stored procedure..
thanx.

When you are returning multiple rows to the calling program, anything other than individual values of output variables.

hi..
thanx for ur reply...
i m new to stored procedure in db2..
so do help me..

i wrote 1 procedure.. let me knw. wht all changes shld be there..

create or replace procedure sp_displayEMP(in deptid integer,out name varchar(200))
language sql
begin

declare deptno integer;
declare ename varchar(200);
declare at_end integer default 0;
declare not_found CONDITION FOR SQLSTATE '02000';

declare c1 cursor for
select name from employee where dept_id = deptid;

declare c2 cursor with return for
select emp_id,emp_name,emp_sal from employee
where dept_id = deptid
order by emp_id;

declare CONTINUE HANDLER FOR not_found SET at_end=1;

open c1;

ins_loop : loop
fetch c1 into ename;
IF at_end=1 THEN
LEAVE ins_loop;
ELSE
set name = ename;
iterate ins_loop;
end if;
end loop;
close c1;

open c2;
return;
close c2;
end


-- lets say 1 dept contains multiple employees..
is this right?
or i want to do any modifications????

I don't understand why you have the output variable name and why you have cursor c1 (but I am not 100% sure what you are trying to do since you didn't explain it). Also, can't you test it yourself?:

This SP uses SAMPLE database:

hi....
thanx a lot for ur help..... it works.........

result set 1 i hav not mentiond into SP


now 1 more question...


can u explain me abt this..

SET param = 'IMPORT FROM NUL OF DEL REPLACE INTO '||full_name;
SET stmt = 'CALL SYSPROC.ADMIN_CMD (?)';

i knw its for truncate.. bt not clear..

nw i have the list of multiple records
hw can i display on console using java (jdbc)

Connection con = null;
CallableStatement cs = null;

cs = con.prepareCall("{CALL iris.sp_displayEMP(?)}");
cs.setInt(1, 1);
cs.execute();

hw to retrieve resultset in java.

there are some chapters about this: see
Data retrieval in JDBC applications - IBM DB2 9.5 for Linux, UNIX, and Windows