We can use cursors within stored procedures. Cursors are used to make a loop within a set of values within the database without returning to the client side thus decreasing the time duration and increasing the effeciency.

If a select query can return one or more record sets, then, a stored procedure can make use of cursors. This cursor will loop through the records and we can make what ever manipulations is expected to be done there.

For this, we need to understand a few terms and syntax. A simple example is as below.

    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

So the things to be noticed here are:

• While declaring a cursor we have to specify a sql query with proper set of select columns.
• Second is to open the cursor.
• FETCH NEXT FROM {cursorname} INTO {set of variables separated by commas} is the syntax to fetch the first record from the query into the cursor.
• Now the cursor is pointing to the first record in the recordset.
• Data in the first record are now stored in the corresponding variables. (in the example we are using only one variable because we have only one column).
• To loop through the entire set of records, we have to use WHILE loop.
• Condition is that the global variable @@FETCH_STATUS will be 0 till it reaches the end and will become -1 as it reaches the end of file.
• So we can form a while loop with this condition as

  	WHILE @@FETCH_STATUS=0
  	WEND
  	

• To move the cursor pointer to the next record, we have to use the FETCH NEXT FROM {cursor_name} into {variables} once into the WHILE loop also.
• Any process can be done within the while loop. For an example, i have used PRINT statement here. You may add the data or you may execute a insert/update/delete statement, Or what ever you require to do.
• Closing and deallocation cursor will help to avoid memory run outs, and many other exceptions. Its a good idea to implement this where ever you work with cursors.