Using Cursor to iterate through records in sql server 2008

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

Here is an 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. This should only be used when you truly need to access one row at a time.