|
Processing Sequentially Through a Set of Records(3) 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 intselect top 1 @CustId=CustomerID from Northwind.dbo.Customersset @RowNum = 0 WHILE @RowNum < 5BEGINset @RowNum = @RowNum + 1print cast(@RowNum as char(1)) + ' ' + @CustIdselect top 1 @CustId=CustomerID from Northwind.dbo.Customerswhere CustomerId > @CustIDEND Here you can see the first SELECT statement selects only the first CustomerID. This ID is placed in the local variable @CustID. The WHILE loop is controled by the local variable @RowNum. Each time through the WHILE loop, the Row Number and CustomerID are printed out. Prior to returning to the top of the WHILE loop I used another "SELECT TOP 1" statement to select the next CustomerID. This SELECT statement uses a WHERE clause on the SELECT statement to select the first CustomerID that is greater than the CustomerID that was just printed. The WHILE loop is process 5 times, allowing the SELECT TOP 1 method to retrieve the top 5 CustomerID's one records at a time. This example produces the same printed output as my prior CURSOR example. Conclusion 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. I use both of these methods, although I find using a SELECT statement to be a little simpler to code. You will need to decide which solution makes the most sense in your environment.
|
