Using Cursor to iterate through records in sql server 2008

Using Cursor to iterate through records in sql server 2008 - Programming Techniques

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

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.