CODEWITHSUNDEEP
sqlsybase
James
James

Reputation: 33

Alternative to cursors in SYBASE?

Say i'm dealing with 10 library cards, each card has customer values (eg. member number, member name ...) and I need to update a value for each card.

If i want to grab all ten from the database but only want to update each row one at a time, is there an alternative to a cursor? I know a while loop might work but how would I be able to grab one row every time it loops until I am done with all 10 cards?

Upvotes: 3

Views: 14569

Answers (2)

Gaurav
Gaurav

Reputation: 461

It is possible to loop on the table using a clustered index on a particular column. Because the clustered index arranges the rows in sorted order, it can be used like an index variable of loop.

declare @uid int
select @uid = 0 -- assume the uids in table are > 0
declare @rowsaf int
select @rowsaf = 1

while @rowsaf > 1
begin
    set rowcount 1
    select @uid = uid from customers where uid > @uid
    select @rowsaf = @@rowcount

    -- update the row using @uid
end

set rowcount 0

Here is the article that explains it in detail.

Upvotes: 0

dbv
dbv

Reputation: 429

Don't need to use cursors. I use this most of the time:

declare @uid int -- this is the type unique index on the table you're updating

-- Copy out the unique ids of the rows you want to update to a temporary table
select uid into #temp from customers -- you can use a where condition here

-- Loop through the rows of the temp table
while exists (select 1 from #temp)
begin
  set rowcount 1
  select @uid = uid from #temp -- pull one uid from the temp table
  set rowcount 0
  delete from #temp where uid = @uid -- delete that uid from the temp table

  -- Do something with the uid you have
  update customers set name = 'Joe Shmoe' where uid = @uid

end

Upvotes: 10

Related Questions