Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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
    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))
  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;

We are running Sybase SQL Anywhere 10.

asked 11 Feb '13, 10:24

Matt's gravatar image

accept rate: 0%

edited 15 Mar '13, 17:03

Mark%20Culp's gravatar image

Mark Culp

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
accept rate: 34%

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 11 Feb '13, 10:24

question was seen: 2,869 times

last updated: 15 Mar '13, 17:03