Which SQL mechanisms allow user to browse tables sequentially?

Answer 1

You can use cursor to achieve this!
Here is an example:

    declare @CustId nchar(5)
    declare @RowNum int
    declare CustList cursor for
    select top 5 CustomerID from Northwind.dbo.Customers
    OPEN CustList
    FETCH NEXT FROM CustList
    INTO @CustId
    set @RowNum = 0
    WHILE @@FETCH_STATUS = 0
    BEGIN
      set @RowNum = @RowNum + 1
      print cast(@RowNum as char(1)) + ' ' + @CustId
      FETCH NEXT FROM CustList
        INTO @CustId
    END
    CLOSE CustList
    DEALLOCATE CustList

Answer 2

You can also use a SELECT statement to process through a set of records one record at a time. To do this I will issue an initial SELECT statement that will return the first row, then a series of follow on SELECT statements where each SELECT statement retrieves the next row. This is done by using the "TOP 1" clause of the SELECT statement, and a WHERE statement.

I will use the same example as above and only return the top 5 CustomerID's from the Northwind database Customers table. In this code I will use two different "SELECT TOP 1" statements and a WHILE loop to return all 5 records. Each record will be processed one at a time.

    declare @CustId nchar(5)
    declare @RowNum int
    select top 1 @CustId=CustomerID from Northwind.dbo.Customers
    set @RowNum = 0
    WHILE @RowNum < 5
    BEGIN
      set @RowNum = @RowNum + 1
      print cast(@RowNum as char(1)) + ' ' + @CustId
      select top 1 @CustId=CustomerID from Northwind.dbo.Customers
                   where CustomerId > @CustID
    END