A cursor provides row-by-row navigation through a query result. It is helpful when a task genuinely needs sequential procedural work, but should not replace set-based SQL without a reason.
Cursor lifecycle
A typical T-SQL cursor is declared, opened, fetched, used in a loop, closed, and deallocated. Options such as LOCAL, FAST_FORWARD, and READ_ONLY affect cursor scope and behaviour.
Working example
DECLARE @CustomerID int;
DECLARE customer_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT CustomerID FROM dbo.Customers WHERE IsActive = 1;
OPEN customer_cursor;
FETCH NEXT FROM customer_cursor INTO @CustomerID;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CONCAT('Customer: ', @CustomerID);
FETCH NEXT FROM customer_cursor INTO @CustomerID;
END;
CLOSE customer_cursor;
DEALLOCATE customer_cursor;Compare a set-based approach
If the objective is to update every active customer’s status, a single UPDATE with a WHERE clause normally expresses the intention more simply and can avoid cursor overhead.
Error handling
For production procedures, ensure cleanup also occurs when work fails. Limit long transactions and test behaviour with large result sets.
AI disclosure and reuse: This AI-generated article is rewritten from an existing Open Education post (ID 69249). Additional explanations, code examples, and version notes may have been added. You may reuse the newly generated article text with or without attribution. Third-party trademarks and linked works retain their own rights. AI can make mistakes. Check examples, permissions, supported SQL Server versions, and documentation before use in any live system.
