About cursor

balaji.kawhale's Avatar, Join Date: Apr 2007
Newbie Member
What are the cursors in sql server?
Under what situation we go for cursors?
What are the types?

Thanks
0
evileye's Avatar, Join Date: Jan 2007
Contributor
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.
0
evileye's Avatar, Join Date: Jan 2007
Contributor
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.
0
0
evileye's Avatar, Join Date: Jan 2007
Contributor
Hell, i luv SQL.

Hope thats enough!
0
sunflowerhot's Avatar
Newbie Member
vow.nice.article.thanks.good.bye
0
venkatesanj@hcl.in's Avatar
Go4Expert Member
Cursors:

Heavy weight objects used in traversing a table based on the values or row values from other tables.

Regards,

Venkatesan Prabu. J