About cursor

Discussion in 'SQL Server' started by balaji.kawhale, Apr 10, 2007.

  1. balaji.kawhale

    balaji.kawhale New Member

    Joined:
    Apr 10, 2007
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    What are the cursors in sql server?
    Under what situation we go for cursors?
    What are the types?

    Thanks
     
  2. evileye

    evileye New Member

    Joined:
    Jan 7, 2007
    Messages:
    51
    Likes Received:
    0
    Trophy Points:
    0
    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.
     
  3. evileye

    evileye New Member

    Joined:
    Jan 7, 2007
    Messages:
    51
    Likes Received:
    0
    Trophy Points:
    0
    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.
     
  4. evileye

    evileye New Member

    Joined:
    Jan 7, 2007
    Messages:
    51
    Likes Received:
    0
    Trophy Points:
    0
  5. evileye

    evileye New Member

    Joined:
    Jan 7, 2007
    Messages:
    51
    Likes Received:
    0
    Trophy Points:
    0
    Hell, i luv SQL. :p

    Hope thats enough!
     
  6. sunflowerhot

    sunflowerhot New Member

    Joined:
    Jun 19, 2007
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    0
    vow.nice.article.thanks.good.bye
     
  7. venkatesanj@hcl.in

    venkatesanj@hcl.in New Member

    Joined:
    Oct 19, 2007
    Messages:
    24
    Likes Received:
    1
    Trophy Points:
    0
    Cursors:

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

    Regards,

    Venkatesan Prabu. J
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice