Ashok's Programming World

Cursors are great way to loop through records in SQL specialy when you need to deal with individual records from certain table.

Here is a example using cursor;

DECLARE @StudentID char(11);
  
DECLARE crs CURSOR READ_ONLY
FOR
SELECT student_id
FROM students
OPEN crs
FETCH NEXT FROM crs
INTO @StudentID
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @StudentID
  FETCH NEXT FROM crs
  INTO @StudentID
END
CLOSE crs
DEALLOCATE crs

The definitions for the terminology are :-

DECLARE CURSOR
    this statement defines the SELECT statement that forms the basis of the cursor.
    You can do just about anything here that you can do in a SELECT statement.
OPEN
    statement executes the SELECT statement and populates the result set.
   
FETCH
    statement returns a row from the result set into the variable.
    You can select multiple columns and return them into multiple variables.
    The variable @@FETCH_STATUS is used to determine if there are any more rows.
    It will contain 0 as long as there are more rows.
    We use a WHILE loop to move through each row of the result set.

READ_ONLY clause is important in the code above.  That improves the performance of the cursor.

CLOSE statement releases the row set

DEALLOCATE statement releases the resources associated with a cursor.

Note **
Please note that cursors are the SLOWEST way to access data inside SQL Server.
The should only be used when you truly need to access one row at a time.