CODEWITHSUNDEEP
stored-proceduresdb2db2-luwdatabase-cursor
Raman
Raman

Reputation: 215

Writing a stored procedure in db2 10.x version

I am new to db2 and am writing a db2 procedure as follows

create procedure ret_mult_rows(in var1 varchar(20),
   out var2 varchar(20),
   out var3 varchar(20))
begin
declare c1 cursor with return to caller for
  select emp_name,emp_city from employee;

open c1;
fetch c1 into var2,var3;
close c1;
end;

Basically as an example the employee table may contain 100 rows and I want to return all 100 rows to the calling program (in this case a java program) However when I execute the stored procedure in stand alone mode using RapidSQL, it is only showing 1 row. What kind of changes are required so that it shows all the 100 rows in the Rapid SQL. Any kind of pointers/answers will be helpful.

Thanks

Upvotes: 0

Views: 532

Answers (1)

Charles
Charles

Reputation: 23823

What platform and version of Db2? Have you look at the manuals?

There's a difference between an stored procedure output variable and returning a (cursor) result set..

Your code should look something like so...

create procedure ret_mult_rows(in var1 varchar(20))
begin
  declare c1 cursor with return to caller for
    select emp_name,emp_city from employee;

  open c1;
end;

You want the cursor itself return to client, you don't want the stored procedure to fetch from it.

possibly applicable only to Db2 for IBM i
Also note that while many examples use C1 as a cursor name, best practice is to have a unique name. If your client happened to call two stored procs and both used C1 you'll run into issues..

Upvotes: 2

Related Questions