implement cursor with sql server 2008

Find the solution of following questions:

What is cursor?

Why cursor?

When to apply cursor?

How to implement cursor?

    "Cursors are an extension to the result sets that provide the mechanism to do operation on a particular row of result set or small block of rows at a time"
   

"Microsoft SQL Server statements produce a complete result set, but there are times when the results are best processed one row at a time. Opening a cursor on a result set allows processing the result set one row at a time. You can assign a cursor to a variable or parameter with a cursor data type.

 
Steps while implementing cursor:

• Declare a cursor that defines a result set.

• Open the cursor to establish the result set.

• Fetch the data into local variables as needed from the cursor, one row at a time.

• Close the cursor when done

• Deallocate the cursor

CURSOR Declaration:
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
    [ FORWARD_ONLY | SCROLL ]
    [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
    [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
    [ TYPE_WARNING ]
    FOR select_statement
    [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

LOCAL:
    Scope of Local cursor is only to batch , stored procedure , or trigger in   which it is declared. And LOCAL cursor automatically deallocated when container block ,sp or trigger terminated.

GLOBAL:
    Specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. The cursor is   only  implicitly deallocated at disconnect.

FORWARD_ONLY
          - Specifies that cursor can only fetch data sequentially from the first to the last row. FETCH NEXT is the only fetch option supported. It is the fastest cursor that you can update.
     The effects of all INSERT, UPDATE, and DELETE statements made by the current user or committed by other users that affect rows in the result set are visible as the rows are fetched from the cursor. Because the cursor cannot be scrolled backward, changes made to rows in the database after the row was fetched are not visible by using the cursor.                    Note: by default it is forward-only.
 
STATIC: Defines a cursor that makes a temporary copy of the data in tempdb to be used by the cursor. Therefore modification made to base table does not reflect to temporary table , and this cursor does not allow modification.
 
DYNAMIC: Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch. The FETCH ABSOLUTE option is not supported with dynamic cursors.


FAST_FORWARD: Specify forward-only , read-only cursor. FAST_FORWARD can not be applied if SCROLL or  FOR_UPDATE is specified.
 
READ_ONLY:  Prevent updates made through this cursor.
 
SCROLL_LOCK: specify that updation  or deletion made through this cursor guarantee to succeed. Because SQL server lock the corresponding row.  
 
OPTIMISTIC: Specifies that positioned updates or deletes made through the cursor do not succeed if the row has been updated since it was read into the cursor. SQL Server does not lock rows as they are read into the cursor.


FOR UPDATE [OF column_name [,...n]]
           Defines updatable columns within the cursor. If OF column_name [,...n] is supplied, only the columns listed allow modifications. If UPDATE is specified without a column list, all columns can be updated, unless the READ_ONLY concurrency option was specified.

Open the CURSOR:
Syntax for opening cursor is
Open <cursor_name>

Fetch row from CURSOR:
FETCH [NEXT | PRIOR | FIRST | LAST |ABSOLUTE{n}|RELATIVE{n}]                        [FROM]    <cursor_name>     [INTO @variable_name[1,2,…n variable]]   
Let us define the terms used in syntax---
NEXT: Returns the immediate  next row .And increment current row pointer to  that row.
PRIOR: Returns the immediate  previous row .And increment current row pointer to  that row.  
FIRST: Returns the first row in the cursor and makes it the current row.
LAST: Returns the last row in the cursor and makes it the current row.
ABSOLUTE{ n}    :Return nth row of result set. If n is positive, counting start from top of result set .If n is negative , counting start from bottom of result set. In both situation returned row is made as new current row. If n is 0 no row is returned.
RELATIVE { n }: Same      functionality as absolute , but only difference is that counting start from current row ,forward of backward ,according to sign of n.                                         NOTE:

If either FORWARD_ONLY or FAST_FORWARD is specified, NEXT is the only FETCH option supported.

If DYNAMIC, FORWARD_ONLY or FAST_FORWARD are not specified, and one of KEYSET, STATIC, or SCROLL are specified, all FETCH options are supported.

DYNAMIC SCROLL cursors support all the FETCH options except ABSOLUTE.

Close the CURSOR:
Closes an open cursor by releasing the current result set and freeing any cursor locks held on the rows on which the cursor is positioned. CLOSE leaves the data structures available for reopening, but fetches and positioned updates are not allowed until the cursor is reopened. CLOSE must be issued on an open cursor; CLOSE is not allowed on cursors that have only been declared or are already closed.

SYNTAX:
      CLOSE   <cursor_name>

DEALLOCATE CURSOR:
Removes a cursor reference. When the last cursor reference is deallocated, the data structures comprising the cursor are released by Microsoft SQL Server.
 

                                                 SIMPLE EXAMPLE
DECLARE @email VARCHAR(100)
DECLARE Temp_cursor CURSOR
    FOR SELECT TOP 10 PrimaryEmail FROM Users
    
    OPEN Temp_cursor
    FETCH NEXT FROM Temp_cursor INTO @email
    WHILE @@FETCH_STATUS=0
    BEGIN
        PRINT @email
        FETCH NEXT FROM Temp_cursor INTO @email
    END
    CLOSE Temp_cursor
    DEALLOCATE Temp_cursor

@@FETCH_STATUS  Values:    return the status  of last cursor FETCH statement.
      0: the FETCH statement was successful
    -1: failed or row was beyond the result set

    -2: the row fetched is missing