I have a cursor that seems to run fine in Sybase IQ.

However, within the cursor's fetch loop I have to update another table. When I perform the insert or the update code, the cursor loop ends. If I comment out the insert AND the update, the code runs fine (but of course, does nothing other than print out what it is doing).

Is updating not allowed within a loop? I am NOT updating the table that is in the cursor. I'm only using the cursor to identify what needs to be updated.

The code is below:

create procedure sp_locktrak as
begin
DECLARE loktrak NO SCROLL CURSOR FOR
SELECT encntrSID, ToUnitDTM, lvunitdtm, currlocid
FROM crr.tmp1
ORDER BY encntrsid, tounitdtm ASC
FOR READ ONLY

DECLARE
@encntrSID INT,
@save_encntrSID INT,
@tounitdtm VARCHAR(20),
@lvunitdtm VARCHAR(20),
@location VARCHAR(20),
@save_location varchar(20),
@save_tounitdtm varchar(20),
@save_lvunitdtm varchar(20),
@first int

select @save_location = '', @save_encntrSID = '', @first = 1

set nocount on

OPEN loktrak

FETCH loktrak
INTO @encntrSID, @tounitdtm, @lvunitdtm, @location

while (SQLCODE = 0)
BEGIN

print '@encounterSID = %1!, @save_encntrSID = %2!, @location = %3!, @save_location = %4!, @@sqlstatus = %5!', @encntrSID, @save_encntrSID, @location, @save_location, SQLCODE

if (@encntrsid = @save_encntrSID) or (@first = 1)
if (@location != @save_location)
begin

insert crr.LocationHistory
values (@encntrSID, convert(date,@tounitdtm,0), convert(date,@lvunitdtm,0), @location)

update crr.LocationHistory
set lvunitdtm = dateadd(mi,-1,convert(date,@save_tounitdtm,0))
where encntrSID = @encntrSID
and tounitdtm = convert(date,@save_tounitdtm,0)

end

print '@encounterSID = %1!, @save_encntrSID = %2!, @location = %3!, @save_location = %4!, @@sqlstatus = %5!', @encntrSID, @save_encntrSID, @location, @save_location, SQLCODE

FETCH loktrak
INTO @encntrSID, @tounitdtm, @lvunitdtm, @location
END


CLOSE loktrak
end

Cursors are evil, im most of the cases, just don´t use them :-)
please use a Set oriented solution like :

--------------- cut ---------------
insert crr.LocationHistory
SELECT encntrSID, ToUnitDTM, lvunitdtm, currlocid
FROM crr.tmp1
AND <IF Condition> -- eg currlocid <> ''


update crr.LocationHistory
set lvunitdtm = dateadd(mi,-1,convert(date,tounitdtm,0))
from crr.LocationHistory t1
inner join ccr.tmp t2 on t1.encntrSID = t2.encntrSID
where t1.encntrSID = t2.encntrSID
and t1.tounitdtm = t2.tounitdtm
AND <IF Condition> -- eg currlocid <> ''
--------------- cut ---------------

if you like you can create a transaction

begin transaction
-- work
commit