Cursor

• Allows row-by-row processing. Control will be with user rather than SQL server
• Different options can be applied while creating a cursor

o Local/global

o Static/dynamic/fast_forward/ketyset driven

o Forward_only/scroll

• Generally it should be avoided as it is affecting a performance a big time
• If another SP is being called within cursor, it will fail. So while loop should be used instead of cursor
• A general syntax

DECLARE cur CURSOR

FOR SELECT a,b FROM TableName

OPEN cur

FETCH NEXT FROM Cur INTO @a, @b

WHILE @@FETCH_STATUS = 0

BEGIN

-- Your logic

FETCH NEXT FROM Cur INTO @a, @b

END

CLOSE cur

DEALLOCATE cur

• In some cases, Cursor will be faster than normal join e.g. 
• when there is some N level of parent-child relationship than rather than joining the same table multiple times, we can use cursor and achieve the results fast
• When you have to insert millions of record in one table then probably you can start cursor and insert a bunch of records (let's say 25K records) in chunks which will be faster than a single INSERT statement
• Even if we have to use cursor, it is recommended that we use WHILE LOOP rather than using cursor