Cursors

25.1 Cursor Lifecycle

DECLARE @ID INT, @Name NVARCHAR(150);
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT ProductID, ProductName FROM dbo.Products WHERE IsActive = 1;
OPEN cur;
FETCH NEXT FROM cur INTO @ID, @Name;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT CONCAT(@ID, ' - ', @Name);
    FETCH NEXT FROM cur INTO @ID, @Name;
END
CLOSE cur;
DEALLOCATE cur;

25.2 Cursor Types and Options

Scope: LOCAL / GLOBAL Scrolling: FORWARD_ONLY / SCROLL (FETCH FIRST, LAST, PRIOR, ABSOLUTE n, RELATIVE n)

Type: STATIC, KEYSET, DYNAMIC, FAST_FORWARD Concurrency: READ_ONLY, SCROLL_LOCKS, OPTIMISTIC

25.3 When to Use (and Not)

✅ Admin tasks: iterate databases, run DBCC per table, call a procedure per row

❌ Row-by-row data processing. Replace with set-based logic, window functions, or a WHILE loop over a keyed temp table.