Interview Question of the Week #003 – How to Write Script for Database Cursor?

I once heard a senior DBA weighing the advantages and disadvantages of cursors. I usually prefer set-based work, but it helps to know a cursor’s mechanics before deciding whether a task needs one. An interview question about syntax is a good place to practice the full lifecycle.

Question: How do you write a database cursor in SQL Server?

Answer: Declare the cursor, open it, fetch a row, repeat while the fetch succeeds, then close and deallocate it. Here is a product example in AdventureWorks2025, limited to five products so you can read the Messages output:

USE AdventureWorks2025;
GO
DECLARE @ProductID int;
DECLARE ProductCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT TOP (5) ProductID
    FROM Production.Product
    ORDER BY ProductID;
OPEN ProductCursor;
FETCH NEXT FROM ProductCursor INTO @ProductID;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @ProductID;
    FETCH NEXT FROM ProductCursor INTO @ProductID;
END;
CLOSE ProductCursor;
DEALLOCATE ProductCursor;

The second FETCH NEXT is essential. Without it, the loop keeps processing the same row. LOCAL limits the cursor’s scope; FAST_FORWARD makes this simple read-only, forward-only example explicit. The printed product IDs depend on the AdventureWorks data installed on your machine. If you use a different database, replace the database and table names.

A WHILE Loop Is Not a Cursor

Interviewers like to follow up with a plain WHILE loop over an integer. It has a similar shape, but it doesn’t use a cursor or fetch rows. This one prints 1, 2, 3, 4 and 5 without reading any table:

DECLARE @intFlag int = 1;
WHILE @intFlag <= 5
BEGIN
    PRINT @intFlag;
    SET @intFlag = @intFlag + 1;
END;

Keep the two concepts separate in an interview. A cursor walks through rows a query returns; a WHILE loop only repeats while a condition is true.

Do You Need a Cursor at All?

For a real task, ask whether a single UPDATE, INSERT ... SELECT or other set-based statement expresses the same rule. A cursor is useful when an action must be performed for each fetched row, not because the requirement mentions several rows.

A cursor is not a loop over numbers, it is a loop over rows, and the second FETCH is what keeps it moving.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.