CURSOR concept and usage in SQL - Step-by-Step Execution

Concept Flow - CURSOR concept and usage

Declare Cursor

↓

Open Cursor

↓

Fetch Row

↓

Process Row

↓

More Rows?

Yes→Fetch Row

No↓

Close Cursor

↓

Deallocate Cursor

This flow shows how a cursor is declared, opened, rows fetched one by one, processed, then closed and deallocated.

Execution Sample

SQL

DECLARE emp_cursor CURSOR FOR SELECT id, name FROM employees;
OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @id, @name;
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT CAST(@id AS VARCHAR) + ' ' + @name;
  FETCH NEXT FROM emp_cursor INTO @id, @name;
END
CLOSE emp_cursor;
DEALLOCATE emp_cursor;

This code declares a cursor to select employee ids and names, then fetches and prints each row one by one.

Execution Table

Step	Action	Cursor State	Variables (@id, @name)	Output/Notes
1	DECLARE cursor emp_cursor	Declared, not open	NULL, NULL	Cursor ready for use
2	OPEN emp_cursor	Open, ready to fetch	NULL, NULL	Cursor opened on employees table
3	FETCH NEXT	Open, fetched first row	1, 'Alice'	First row fetched
4	Check @@FETCH_STATUS	0 (success)	1, 'Alice'	Continue loop
5	PRINT @id + ' ' + @name	Open	1, 'Alice'	Output: '1 Alice'
6	FETCH NEXT	Open, fetched second row	2, 'Bob'	Second row fetched
7	Check @@FETCH_STATUS	0 (success)	2, 'Bob'	Continue loop
8	PRINT @id + ' ' + @name	Open	2, 'Bob'	Output: '2 Bob'
9	FETCH NEXT	Open, fetched third row	3, 'Carol'	Third row fetched
10	Check @@FETCH_STATUS	0 (success)	3, 'Carol'	Continue loop
11	PRINT @id + ' ' + @name	Open	3, 'Carol'	Output: '3 Carol'
12	FETCH NEXT	Open, no more rows	NULL, NULL	No more rows
13	Check @@FETCH_STATUS	-1 (no data)	NULL, NULL	Exit loop
14	CLOSE emp_cursor	Closed	NULL, NULL	Cursor closed
15	DEALLOCATE emp_cursor	Deallocated	NULL, NULL	Cursor resources freed

💡 Loop ends when FETCH NEXT returns no more rows (@@FETCH_STATUS = -1)

Variable Tracker

Variable	Start	After Step 3	After Step 6	After Step 9	After Step 12	Final
@id	NULL	1	2	3	NULL	NULL
@name	NULL	'Alice'	'Bob'	'Carol'	NULL	NULL
@@FETCH_STATUS	NULL	0	0	0	-1	-1

Key Moments - 3 Insights

Why do we need to check @@FETCH_STATUS after each FETCH?

Because @@FETCH_STATUS tells us if the FETCH succeeded or if there are no more rows. Without checking, the loop might run forever or process invalid data. See execution_table rows 4, 7, 10, 13.

What happens if we forget to CLOSE or DEALLOCATE the cursor?

The cursor remains open and consumes resources, which can cause performance issues. Closing and deallocating frees those resources. See execution_table rows 14 and 15.

Why are variables @id and @name NULL after the last FETCH?

Because FETCH returns no data when rows are exhausted, so variables are set to NULL. This signals the end of data. See execution_table row 12.

Visual Quiz - 3 Questions

Look at the execution_table, what are the values of @id and @name after step 6?

A 2, 'Bob'

B 1, 'Alice'

C 3, 'Carol'

D NULL, NULL

💡 Hint

Check the Variables column in execution_table row 6

At which step does the loop exit because there are no more rows?

A Step 15

B Step 10

C Step 13

D Step 9

💡 Hint

Look at the @@FETCH_STATUS value and loop exit note in execution_table rows 12 and 13

If we skip the CLOSE step, what happens to the cursor?

A It deletes the data

B It remains open and uses resources

C It automatically closes

D It fetches rows again

💡 Hint

Refer to key_moments about resource usage and execution_table row 14

Concept Snapshot

CURSOR usage in SQL:
1. DECLARE cursor for a SELECT query
2. OPEN cursor to start
3. FETCH rows one by one into variables
4. Check @@FETCH_STATUS to continue or stop
5. CLOSE cursor when done
6. DEALLOCATE cursor to free resources
Cursors let you process query results row-by-row.

Full Transcript

A cursor in SQL is a tool to process query results one row at a time. First, you DECLARE the cursor with a SELECT statement. Then you OPEN it to start reading. You FETCH each row into variables and check @@FETCH_STATUS to see if more rows exist. If yes, you process the row and FETCH the next. When no rows remain, you CLOSE and DEALLOCATE the cursor to free resources. This step-by-step process helps handle data row-wise instead of all at once.

Practice

1. What is the main purpose of using a CURSOR in SQL?

easy

A. To process query results row by row

B. To speed up bulk data insertion

C. To create new tables dynamically

D. To automatically backup the database

Solution

1. Step 1: Understand what a cursor does

   A cursor allows you to handle each row returned by a query individually, instead of all at once.

2. Step 2: Compare options to cursor purpose

   Options B, C, and D describe tasks unrelated to row-by-row processing, which is the cursor's main use.

3. Final Answer:

   To process query results row by row -> Option A

4. Quick Check:

   Cursor = row-by-row processing [OK]

Hint: Remember: Cursor = handle rows one at a time [OK]

Common Mistakes:

• Thinking cursor speeds up bulk inserts
• Confusing cursor with table creation
• Assuming cursor automates backups

2. Which of the following is the correct order of cursor operations in SQL?

easy

A. DECLARE, FETCH, OPEN, CLOSE, DEALLOCATE

B. OPEN, DECLARE, FETCH, CLOSE, DEALLOCATE

C. DECLARE, OPEN, FETCH, CLOSE, DEALLOCATE

D. FETCH, DECLARE, OPEN, CLOSE, DEALLOCATE

Solution

1. Step 1: Recall the cursor lifecycle

   You first DECLARE the cursor, then OPEN it to start fetching rows, FETCH rows one by one, CLOSE it when done, and finally DEALLOCATE to free resources.

2. Step 2: Match the correct sequence

   Only DECLARE, OPEN, FETCH, CLOSE, DEALLOCATE follows this exact order. Other options mix the steps incorrectly.

3. Final Answer:

   DECLARE, OPEN, FETCH, CLOSE, DEALLOCATE -> Option C

4. Quick Check:

   Cursor lifecycle order = C [OK]

Hint: Remember: Declare first, then open, fetch, close, deallocate [OK]

Common Mistakes:

• Opening cursor before declaring
• Fetching before opening
• Not deallocating after closing

3. Given the following SQL cursor code snippet, what will be the output?

DECLARE @Name VARCHAR(50);
DECLARE name_cursor CURSOR FOR SELECT Name FROM Employees WHERE Department = 'Sales';
OPEN name_cursor;
FETCH NEXT FROM name_cursor INTO @Name;
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @Name;
  FETCH NEXT FROM name_cursor INTO @Name;
END
CLOSE name_cursor;
DEALLOCATE name_cursor;

Assuming the Employees table has Sales department names: 'Alice', 'Bob', 'Carol'.

medium

A. Only 'Alice' is printed

B. Syntax error due to missing FETCH

C. No output because cursor is not opened

D. Alice Bob Carol (each printed on a new line)

Solution

1. Step 1: Understand cursor fetch loop

   The cursor fetches each row where Department = 'Sales'. It prints the @Name variable for each fetched row.

2. Step 2: Identify the printed names

   Since the table has 'Alice', 'Bob', and 'Carol' in Sales, all three names will be printed one by one.

3. Final Answer:

   Alice Bob Carol (each printed on a new line) -> Option D

4. Quick Check:

   Cursor prints all fetched rows [OK]

Hint: Cursor loops print all fetched rows until none left [OK]

Common Mistakes:

• Assuming only first row prints
• Forgetting to open cursor
• Confusing fetch status check

4. Identify the error in the following cursor code:

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

medium

A. Missing FETCH NEXT inside the WHILE loop

B. Cursor not declared properly

C. Cursor not opened before fetching

D. No error, code runs fine

Solution

1. Step 1: Check fetch inside loop

   The code fetches once before the loop but does not fetch again inside the loop, causing an infinite loop.

2. Step 2: Identify missing statement

   To move to next row, FETCH NEXT must be inside the WHILE loop after PRINT.

3. Final Answer:

   Missing FETCH NEXT inside the WHILE loop -> Option A

4. Quick Check:

   Fetch inside loop needed to advance cursor [OK]

Hint: Always fetch next row inside the loop to avoid infinite loop [OK]

Common Mistakes:

• Forgetting FETCH NEXT inside loop
• Assuming one fetch is enough
• Not opening cursor before fetch

5. You want to update the salary of employees one by one using a cursor. Which approach correctly uses a cursor for this task?

hard

A. Open cursor, but never fetch rows, then update salary

B. Declare cursor for SELECT, open it, fetch each employee, update salary inside loop, close and deallocate

C. Declare cursor, but update salary before opening cursor

D. Use a single UPDATE statement without cursor for all employees

Solution

1. Step 1: Understand row-by-row update need

   When updating salaries individually, you must fetch each employee row and update inside the loop.

2. Step 2: Identify correct cursor usage

   Declare cursor for SELECT, open it, fetch each employee, update salary inside loop, close and deallocate correctly declares, opens, fetches, updates inside loop, then closes and deallocates cursor.

3. Final Answer:

   Declare cursor for SELECT, open it, fetch each employee, update salary inside loop, close and deallocate -> Option B

4. Quick Check:

   Cursor updates row-by-row inside loop [OK]

Hint: Update inside fetch loop after opening cursor [OK]

Common Mistakes:

• Trying to update before opening cursor
• Not fetching rows before update
• Using cursor but updating all at once