Sybase : Iterating over table without using cursor

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.

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.

End the loop and set row count to zero. Setting to zero returns all the rows affected.

Complete listing of the code below.


Photo credit: katerha / / CC BY