|
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. |
|
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. |
