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.

asked 11 Feb '13, 10:24

Matt's gravatar image

Matt
36226
accept rate: 0%

edited 15 Mar '13, 17:03

Mark%20Culp's gravatar image

Mark Culp
23.4k9132275


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.

permanent link

answered 11 Feb '13, 10:38

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676
accept rate: 33%

edited 11 Feb '13, 10:45

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×25
×18

question asked: 11 Feb '13, 10:24

question was seen: 1,059 times

last updated: 15 Mar '13, 17:03