My blog has moved!

You will be automatically redirected to the new address. If that does not occur, visit
http://ashokbasnet.com.np
and update your bookmarks.

Thursday, June 28, 2012

Using Cursor to iterate through records in sql server 2008

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.

0 comments :

Post a Comment