CURSOR concept and usage in SQL

Introduction

A cursor lets you go through rows in a table one by one. It helps when you want to do something with each row separately.

When you need to process each row of a query result step by step.

When you want to update or check rows one at a time.

When a task depends on the data from the previous row.

When you cannot do the work with a single SQL statement.

When you want to print or log each row's data during processing.

Syntax

SQL

DECLARE cursor_name CURSOR FOR select_statement;
OPEN cursor_name;
FETCH NEXT FROM cursor_name INTO variable_list;
-- Repeat FETCH as needed
CLOSE cursor_name;
DEALLOCATE cursor_name;

You first declare the cursor with a SELECT query.

Then you open it to start reading rows.

FETCH gets the next row into variables.

Close and deallocate when done to free resources.

Examples

Declare a cursor named emp_cursor to select employee IDs and names.

SQL

DECLARE emp_cursor CURSOR FOR
SELECT employee_id, name FROM employees;

Open the cursor and fetch the first row into variables.

SQL

OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @emp_id, @emp_name;

Loop through all rows, printing each employee's name.

SQL

WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT 'Employee: ' + @emp_name;
  FETCH NEXT FROM emp_cursor INTO @emp_id, @emp_name;
END

Close and remove the cursor when finished.

SQL

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

Sample Program

This program goes through each employee row, printing their ID and name one by one.

SQL

DECLARE @emp_id INT, @emp_name NVARCHAR(50);
DECLARE emp_cursor CURSOR FOR
SELECT employee_id, name FROM employees;

OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @emp_id, @emp_name;

WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT 'Employee ID: ' + CAST(@emp_id AS NVARCHAR(10)) + ', Name: ' + @emp_name;
  FETCH NEXT FROM emp_cursor INTO @emp_id, @emp_name;
END

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

Important Notes

Cursors can be slow for large data because they process rows one at a time.

Always close and deallocate cursors to avoid using extra memory.

Try to use set-based SQL operations first before using cursors.

Summary

A cursor lets you handle query rows one by one.

You declare, open, fetch rows, then close and deallocate.

Use cursors when row-by-row processing is needed.