Cursor is not the only way to iterate over data in a table in sybase. It is possible to iterate over a table without using cursor. This article shows an alternative to cursor in sybase.
If a table has a clustered index defined on a column (or set of columns) then the rows in that table are organized in a sorted order by the indexed columns. This property of the clustered index can be used to iterate over the rows of a table.
Let us consider a table as below.
| order_id | product_name | quantity | status |
|---|---|---|---|
| 1 | Soap | 4 | Unknown |
| 2 | Toothpaste | 2 | Unknown |
| 3 | Shampoo | 3 | Unknown |
The table has a clustered index on order_id column. The status column needs to be updated one row at a time. This may be required if the status is received as an output of a stored procedure.
Pick up a control variable and set it to minimum possible value. In this case a variable to hold order id, set to zero. Set rowcount to 1. This is the real trick. It causes only one row to be returned for a select query.
declare @prev_order_id int select @prev_order_id = 0 declare @nrows int select @nrows = 1 while @nrows > 0 begin set rowcount 1
Now select data from table and perform operation using the data. Also select number of rows affected from the select operation. This must be stored in another variable because value of @@rowcount may subsequently change.
select @product = product_name, @prev_order_id = order_id from ORDER where order_id > @prev_order_id
select @nrows = @@rowcount
-- perform operation using @product
End the loop and set row count to zero. Setting to zero returns all the rows affected.
end set rowcount 0
Complete listing of the code below.
declare @prev_order_id int
select @prev_order_id = 0
declare @nrows int
select @nrows = 1
while @nrows > 0
begin
set rowcount 1
select @product = product_name, @prev_order_id = order_id from ORDER where order_id > @prev_order_id
select @nrows = @@rowcount
-- perform operation using @product
end
set rowcount 0