Cursor in SQL Server

SET NOCOUNT ON;

DECLARE @ID int, @Name nvarchar(50),

    @Message varchar(80) ;

DECLARE CursorName CURSOR FOR

SELECT ID, Name

FROM TableName

 ORDER BY ID;

OPEN CursorName

FETCH NEXT FROM CursorName

INTO @ID, @Name

WHILE @@FETCH_STATUS = 0

BEGIN

    PRINT ' '

    SELECT @Message = '----- Cursor loop: ' +

        @Name

     PRINT @Message

    -- Declare an inner cursor based  

    -- on vendor_id from the outer cursor.
    /* Write here your logic */
        -- Get the next vendor.
    FETCH NEXT FROM CursorName

    INTO @ID, @Name

END

CLOSE CursorName;

DEALLOCATE CursorName;

• DECLARE CURSOR statement - Populate the cursor with values that will be evaluated  

• NOTE - There are an equal number of variables in the DECLARE <cursor_name> CURSOR FOR statement as there are in the SELECT statement.  This could be 1 or many variables and associated columns. 

• OPEN statement - Open the cursor to begin data processing  

• FETCH NEXT statements - Assign the specific values from the cursor to the variables

• @@FETCH_STATUS : will check if there is any row in the current Open cursor   

• Returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection. 

• Status :

           0     The FETCH statement was successful.

           -1    The FETCH statement failed or the row was beyond the result set.

                      -2    The row fetched is missing.

• NOTE - This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement 

• WHILE statement - Condition to begin and continue data processing  

• BEGIN...END statement - Start and end of the code block

• NOTE - Based on the data processing multiple BEGIN...END statements can be used Data processing 

• CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened  

• DEALLOCATE statement - Destroys the cursor