What are the cursors in sql server? Under what situation we go for cursors? What are the types? Thanks
SQL Server is very good at handling sets of data.� For example, you can use a single SELECT statement to update many rows of data.� There are times when you want to loop through a series of records a perform processing for each record.� In this case you can use a cursor.� The basic syntax of a cursor is: DECLARE @AuthorID char(11) DECLARE c1 CURSOR FOR SELECT au_id FROM authors OPEN c1 FETCH NEXT FROM c1 INTO @AuthorID WHILE @@FETCH_STATUS = 0 BEGIN PRINT @AuthorID FETCH NEXT FROM c1 INTO @AuthorID END CLOSE c1 DEALLOCATE c1 The DECLARE CURSOR 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.� The OPEN statement statement executes the SELECT statement and populates the result set.� The 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. In this example, I just print the contents of the variable.� You can execute any type of statement you wish here.� In a recent script I wrote I used a cursor to move through the rows in a table and call a stored procedure for each record passing it the primary key.� Given that cursors are not very fast and calling a stored procedure for each record in a table is also very slow, my script was a resource hog.� However, the stored procedure I was calling was written by the software vendor and was a very easy solution to my problem.� In this case, I might have something like this: EXEC spUpdateAuthor (@AuthorID) instead of my Print statement. The CLOSE statement releases the record set and the DEALLOCATE statementreleases the resources associated with a cursor. If you are going to update the records as you go through them, you can use the UPDATE clause when you declare a cursor. DECLARE c1 CURSOR FOR SELECT au_id, au_lname FROM authors FOR UPDATE OF au_lname You can code your UPDATE statement to update the current record in the cursor like this UPDATE authors SET au_lname = UPPER(Smith) WHERE CURRENT OF c1 If you have a cursor that will be read only you can speed it up by using the READ_ONLY clause:� DECLARE c1 CURSOR READ_ONLY FOR SELECT au_id FROM authors That covers the basics of cursors.� You can check Books Online for more detailed information.
Types : Static cursors Dynamic cursors Forward-only cursors Keyset-driven cursors Static cursors A static cursor always displays the result set as it was when the cursor was opened. Static cursors are always read-only. Dynamic Cursors Dynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor. Forward-only Cursors A forward-only cursor does not support scrolling; it supports only fetching the rows serially from the start to the end of the cursor Keyset-driven Cursors The keyset is the set of the key values from all the rows that qualified for the SELECT statement at the time the cursor was opened.
Cursors: Heavy weight objects used in traversing a table based on the values or row values from other tables. Regards, Venkatesan Prabu. J
