A common requirement in MS-SQL is the need to step through a list of records one at a time. This is done via the use of cursors.
The idea is that the list of records is generated, which is the cursor definition. Then, each record is processed one at a time, so that the appropriate actions can be performed.
There are a few steps involved with doing this. First, a set of variables must be defined to hold the records for the cursor as they are processed. Next, the cursor must be defined, and opened. Then the first record needs to be read, followed by the processing loop. Within the processing loop, whatever actions are required are performed, and then the next record is read. After the processing loop exits, the cursor needs to be closed, and deallocated to remove it.
To illustrate, let us assume that we have an invoice table (INVOICES), which contains the field ticket number (TICKET_NO), and that we want to step through each ticket.
First we need to declare a variable to hold the ticket numbers as they are read, and then the cursor itself, and then open the cursor:
DECLARE @TICKET_NO varchar(20)
DECLARE DATA_CURSOR CURSOR LOCAL FOR SELECT TICKET_NO from INVOICES
OPEN DATA_CURSOR
Now that we have the cursor defined, and opened, we need to get the first record:
FETCH NEXT FROM DATA_CURSOR INTO @TICKET_NO
Then, we loop based on the status of the "fetch" command. Within this loop, do your processing, and then perform the "fetch" command again:
WHILE @@FETCH_STATUS=0
BEGIN
(do whatever processing you want here)
FETCH NEXT FROM DATA_CURSOR INTO @TICKET_NO
END
The loop will exit, when the "fetch" command no longer returns any values to process. At that point, all that is left is to close the cursor, and clean up:
CLOSE DATA_CURSOR
DEALLOCATE DATA_CURSOR
That is all that there is to being able to step through data row-by-row, if you need to.
Cursors are not exactly efficient when they are executed, however. If possible, it is better to use an "update-join", or similar approach if possible. There are times that it can not be avoided, though, and you have to use a cursor to do what you want.
Dave.