USING CURSOR IN MSSQL 2008 R2

Using Cursor is one of my favorite option while i need to perform a loop for an undefined sets of data, though this might cost you operational in-effeciency and memory leakage problems. Hence i suggest you DO NOT use cursor until you need to perform row by row operations on a list of data.
Before Starting with an example, lets go through basic working principle of using cursor in MSSQL.
First you need to define a temporary variable (which is a normal variable) to fetch particular column value into it for operation.
Then you define the cursor name along with data-type you need to hold and the query that fetches a list of the values.
And after you get all these values you fetch data until all the data have been fetched.
Lets see an Example.

— temporary variable that holds the data from cursor 
DECLARE @ID int


— now define cursor and its source
DECLARE cursor_Author_ID
                  CURSOR READ_ONLY FOR 
                 SELECT au_id FROM authors


—open cursor
OPEN cursor_Author_ID
 
–fetch first Data
 FETCH NEXT FROM cursor_Author_ID INTO 
               @ID
–keep fetching data until all data have been read
 WHILE @@FETCH_STATUS = 0 
              BEGIN
               —write your code here, use the ID

                                                                
                   –keep fetching data until all data have been read  

                    FETCH NEXT FROM cursor_Author_ID  INTO  
                    @ID  
           END 
—you need to close the cursor like any file, after opening it
 CLOSE cursor_Author_ID  
— deallocate the cursor to release all the resources used by it and 
— also reuse the for other variables
 DEALLOCATE  cursor_Author_ID