SQL Server › Cursors

What are the basic steps required to declare, open, use, and properly clean up a cursor?

Intermediate

Answer

You declare the cursor with a defining query, open it to begin execution, fetch rows one at a time in a loop while checking the fetch status to know when to stop, and finally close and deallocate the cursor to release the resources it was using, which is an important cleanup step many beginners forget.

DECLARE @ProductId INT;
DECLARE product_cursor CURSOR FOR SELECT ProductId FROM Products;
OPEN product_cursor;
FETCH NEXT FROM product_cursor INTO @ProductId;
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @ProductId;
  FETCH NEXT FROM product_cursor INTO @ProductId;
END
CLOSE product_cursor;
DEALLOCATE product_cursor;

Real-world example A maintenance script processes each product one at a time using a cursor, carefully following the open, fetch, close, and deallocate sequence to avoid leaving unused resources allocated on the server.

Common follow-ups: What happens if you forget to close and deallocate a cursor?;What does the @@FETCH_STATUS value actually indicate?

Stored Procedures & Functions;Error Handling with TRY CATCH