SQL Cursor: Syntax, Usage, and Examples

The SQL cursor provides a way to process query results row by row. While set-based operations are preferred in SQL, cursors come in handy when you need to perform row-level logic that can't easily be expressed in a single statement.

How to Use a SQL Cursor

To use a SQL cursor, you typically go through five steps: declare the cursor, open it, fetch each row, process the row, and close the cursor when you're done.

Here’s a common pattern for declaring and using a cursor in SQL Server:

DECLARE cursor_name CURSOR FOR
SELECT column1, column2
FROM your_table;

OPEN cursor_name;

FETCH NEXT FROM cursor_name INTO @var1, @var2;

WHILE @@FETCH_STATUS = 0
BEGIN
  -- Do something with @var1 and @var2
  FETCH NEXT FROM cursor_name INTO @var1, @var2;
END;

CLOSE cursor_name;
DEALLOCATE cursor_name;

Use a SQL cursor when your task requires step-by-step logic for each row of data.

When to Use a Cursor in SQL

Sometimes, you run into problems that set-based queries just can't handle efficiently. That’s where cursors can help.

Applying Complex Business Logic

Use a cursor in SQL when each row requires different calculations or logic paths. For example, applying tax rates or pricing rules that depend on customer tier, item type, or other dynamic conditions.

Row-by-Row Data Migration or Auditing

Cursors work well when you're updating multiple tables row by row, inserting audit entries, or checking conditions before updating records.

Sequential Processing with Dependencies

If rows need to be processed in order—like incrementally assigning values, creating logs, or tracking dependencies—then a cursor in SQL query logic gives you that control.

Examples of SQL Cursor Usage

Let’s walk through a few examples of cursors in SQL Server and how they behave in real-world scenarios.

SQL Cursor Example: Logging Customer Status

Suppose you want to loop through customers and log a message for each one based on their status:

DECLARE @id INT, @status VARCHAR(20);

DECLARE customer_cursor CURSOR FOR
SELECT customer_id, status
FROM customers;

OPEN customer_cursor;

FETCH NEXT FROM customer_cursor INTO @id, @status;

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @status = 'inactive'
    PRINT 'Customer ' + CAST(@id AS VARCHAR) + ' is inactive.';
  ELSE
    PRINT 'Customer ' + CAST(@id AS VARCHAR) + ' is active.';

  FETCH NEXT FROM customer_cursor INTO @id, @status;
END;

CLOSE customer_cursor;
DEALLOCATE customer_cursor;

This SQL cursor example walks through each row and performs conditional logging.

Cursor in SQL Server for Row Updates

You can also use a cursor in SQL Server to update records row by row:

DECLARE @id INT, @bonus DECIMAL(10,2);

DECLARE bonus_cursor CURSOR FOR
SELECT employee_id, performance_score
FROM employees;

OPEN bonus_cursor;

FETCH NEXT FROM bonus_cursor INTO @id, @bonus;

WHILE @@FETCH_STATUS = 0
BEGIN
  UPDATE employees
  SET bonus_amount = CASE
    WHEN @bonus > 90 THEN 1000
    WHEN @bonus > 75 THEN 750
    ELSE 500
  END
  WHERE employee_id = @id;

  FETCH NEXT FROM bonus_cursor INTO @id, @bonus;
END;

CLOSE bonus_cursor;
DEALLOCATE bonus_cursor;

Use this approach when batch updates require custom logic per row.

Cursor with Conditional Insert

If you need to move data conditionally from one table to another:

DECLARE @product_id INT, @stock INT;

DECLARE stock_cursor CURSOR FOR
SELECT product_id, stock
FROM inventory;

OPEN stock_cursor;

FETCH NEXT FROM stock_cursor INTO @product_id, @stock;

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @stock < 10
  BEGIN
    INSERT INTO low_stock_alerts(product_id, alert_time)
    VALUES (@product_id, GETDATE());
  END

  FETCH NEXT FROM stock_cursor INTO @product_id, @stock;
END;

CLOSE stock_cursor;
DEALLOCATE stock_cursor;

This cursor in SQL lets you selectively insert based on business rules.

Learn More About SQL Cursors

Cursors vs. Set-Based Queries

Cursors give you flexibility, but at a cost. Set-based operations are faster and more efficient because they work on batches of rows. Use a SQL cursor only when set-based alternatives are too complicated or impossible.

For example, to increase all prices by 10%, a simple update is far more efficient:

UPDATE products
SET price = price * 1.1;

No cursor needed.

But if your logic requires checking external data or logging per row, a cursor makes more sense.

Types of SQL Cursors

In SQL Server, you have several cursor types:

• Static: Takes a snapshot of the result set. Doesn't reflect changes after the cursor opens.
• Dynamic: Reflects changes to the underlying data while the cursor is open.
• Forward-only: Only allows moving forward through the result set.
• Keyset-driven: Tracks keys of rows in the result set, allowing updates.

Choose the cursor type that matches your performance needs.

Example:

DECLARE my_cursor CURSOR LOCAL STATIC READ_ONLY FOR
SELECT name FROM products;

Using STATIC and READ_ONLY avoids locking and improves performance when updates aren’t needed.

Cursor in SQL Query Performance Tips

Cursors are resource-intensive. If you must use one:

• Declare them as LOCAL, FAST_FORWARD, or READ_ONLY where possible.
• Use FETCH NEXT carefully to avoid infinite loops.
• Always CLOSE and DEALLOCATE them to free resources.

If you find yourself using a cursor often, ask: can a JOIN, CTE, or window function solve this instead?

Alternatives to SQL Cursor

Here are some alternatives to reduce dependency on cursors:

• Use window functions (ROW_NUMBER(), RANK(), etc.) for ranking or ordering tasks.
• Leverage common table expressions (CTEs) for recursive operations.
• Write MERGE statements for update/insert/delete combinations.
• Use temporary tables or table variables for intermediate data storage.

If your SQL server supports them, stored procedures with set-based logic can often replace cursors entirely.

SQL Server Cursor Example with FETCH PRIOR

Not all cursors need to go forward. With SCROLLable cursors, you can go backward or jump around:

DECLARE order_cursor CURSOR SCROLL FOR
SELECT order_id FROM orders;

OPEN order_cursor;

FETCH LAST FROM order_cursor; -- Jump to the last row
FETCH PRIOR FROM order_cursor; -- Move to the previous row

CLOSE order_cursor;
DEALLOCATE order_cursor;

This is useful when you need more control over navigation through results.

Nested Cursors

You can nest one cursor inside another, but do so with caution. Performance drops significantly when nested cursors run across large datasets.

Example:

-- Outer cursor for customers
-- Inner cursor for orders for each customer

You might solve this more efficiently using JOINs.

The SQL cursor remains a tool you can reach for when logic gets too specific or row-based. Use it sparingly, but confidently, when the situation calls for it. With clear structure and clean management, a cursor in SQL helps bridge the gap between row-by-row logic and real-world complexity.