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;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | 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.
Related Article
destination source:https://www.programming-techniques.com/2012/06/using-cursor-to-iterate-through-records-in-sql-server-2008.html