|
Processing Sequentially Through a Set of Records(2) 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. DECLARE cursor_name CURSOR FOR select_statement The cursor_name is the name you want to associate with the cursor. The select_statement is the query that will determine the rows that make up the cursor. Note there are other parameters/options associated with the DECLARE CURSOR statement that help define more complicated cursor processing than I will be covering in this article. For these additional options please read Microsoft SQL Server Books Online. Let's review a fairly simple cursor example. This example will define a cursor that contains the top 5 Customer_Id's in the Customer table in the Northwind database. It will then process through each record displaying a row number and the CustomerID for each. Here is the code to do this. declare @CustId nchar(5)declare @RowNum intdeclare CustList cursor forselect top 5 CustomerID from Northwind.dbo.CustomersOPEN CustListFETCH NEXT FROM CustList INTO @CustIdset @RowNum = 0 WHILE @@FETCH_STATUS = 0BEGINset @RowNum = @RowNum + 1print cast(@RowNum as char(1)) + ' ' + @CustIdFETCH NEXT FROM CustListINTO @CustIdENDCLOSE CustListDEALLOCATE CustList Here are the results that are generated from the print statement when I run it against my Northwind Database. 1 ALFKI2 ANATR3 ANTON4 AROUT5 BERGS Let's look at the above code in a little more detail. I first declared a cursor called "CustList". The "CustList" cursor is populated using a SELECT statement that uses the TOP clause to return only the top 5 CustomerId's. Next the cursor is opened. Each record in the "CustList" cursor is retrieved, one record at a time, using the "FETCH NEXT" next statement. The "FETCH NEXT" statement populates the local variable @CustID with the CustomerID of the current record being fetched. The @@FETCH_STATUS variable controls whether the WHILE loop is executed. @@FETCH_STATUS is set to zero when a record is sUCcessfully retrieved from the cursor "CustList". Inside the WHILE loop the @RowNum variable is incremented by 1 for each record processed. The calculated Row Number and @CustId are then printed out. Lastly, a "FETCH NEXT" statement is used to retrieve the next row before the next cycle of the WHILE loop. This process continues one record at a time until all records in cursor "CustList" have been processed. Using a Select Statement
| 
