Calling A Procedure For Every Row In A Select Statement Inside A Stored Proc

[Former Member]

I have tried quite a few different examples using FOR and CURSOR but have not had much success.

For a select statement I would like to call a procedure using date from the current row.

[pseudo code]
select customerid, name from customer
loop
    call CheckCustomer(customerid, name)
end loop
[/pseudo code]

Below is the best I could get but I believe due to a commit in the CheckCustomer procedure it only ever gets called for the first row retrieved.

alter PROCEDURE "spaceman"."testf" ()
result (ret char(20))
begin
  for l1 as cc cursor for 
    select ocustomerid, ocontractid, onextdiarydate, ochargedto, oinvoicefrequency from contract do
      call CheckCustomer(ocustomerid, ocontractid, 'admin', onextdiarydate);
  end for;
  select 1;
end

We are running Sybase SQL Anywhere 10.

[VolkerBarth]

FOR statements do always leave cursors opened over transaction boundaries (they declare the underlying as "WITH HOLD") by design, so that should usually be sufficient for the case you are dscribing, i.e. the commit inside the procedure shouldn't interfere with the loop... - We use constructions like this very often in our own procedures - and yes, they do work:)

Cf. the discussion in that FAQ.

So there seems to be a different problem here, methinks.