In this code I will use two different "SELECT TOP 1" statements and a WHILE loop to return all 5 records. Here you can see the first SELECT statement selects only the first Customer ID. The WHILE loop is controled by the local variable @Row Num.Each time through the WHILE loop, the Row Number and Customer ID are printed out.
Inside the WHILE loop the @Row Num variable is incremented by 1 for each record processed.
The calculated Row Number and @Cust Id are then printed out.
The first method I will discuss uses a cursor to process through a set of records one record at a time.
A cursor is basically a set of rows that you define based on a record set returned from a query.
This default functionality will only read from the first row to the last row in a cursor, one row at a time.
I will leave additional cursor topics to another article series.
To define a cursor the DECLARE CURSOR statement is used.
Here is the basic format for the simple cursor topic I will be discussing in this article.
Prior to returning to the top of the WHILE loop I used another "SELECT TOP 1" statement to select the next Customer ID.
This SELECT statement uses a WHERE clause on the SELECT statement to select the first Customer ID that is greater than the Customer ID that was just printed.
Hopefully this article has given you some ideas on how to use a CURSOR, and a SELECT statement to process through a set of records.