What Is Cursor In SQL

What is Cursor SQL, let’s look at another definition. It's a database feature that allows developers to control record navigation, updation, and retrieval by providing a way to process individual rows returned by database system queries. A SQL Server Cursor essentially acts as a pointer to one row in a set of rows.

The lifecycle of a Cursor in SQL comprises several steps:

1. DECLARE
2. OPEN
3. FETCH
4. CLOSE
5. DEALLOCATE

DECLARE CURSOR

The first step in the life cycle of a Cursor in SQL Server is the DECLARE CURSOR statement. It is used to define a Cursor in SQL.

The syntax to declare SQL Server CURSOR looks very cumbersome. As this is a beginner-level article we are going to skip its details. Better to start directly from a real word example!

After reading this article, we recommend you also look at the SQL Server's official documentation for detailed syntax and different types of Cursors in SQL Server (STATIC, KEYSET, DYNAMIC, FAST_FORWARD).

The syntax of a DECLARE SQL CURSOR command in SQL Server looks like the following (don’t be afraid and keep reading, you will learn a lot):

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
[ TYPE_WARNING ]
FOR select_statement 
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]

OPEN CURSOR

The second step is the OPEN CURSOR statement, which is used to open the SQL Server Cursor. This statement executes the SQL SELECT statement defined in the cursor declaration and populates the result set. It sets the position of the Cursor in SQL Server to just before the first row.

The OPEN CURSOR syntax is simply:

OPEN cursor_name

FETCH CURSOR

The FETCH statement is used to retrieve/fetch data into variables and move to a row in the SQL Server CURSOR .

The simplest syntax of the FETCH statement is

FETCH [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar } ]
FROM cursor_name
INTO @Variable[,..n]

CLOSE CURSOR

The CLOSE statement is used to deactivate the Cursor in SQL Server and free the resources. It does not delete the definition of the cursor.

The syntax is

CLOSE cursor_name

DEALLOCATE CURSOR

Finally, the DEALLOCATE statement is used to delete the Cursor in SQL Server from memory. It removes the definition of the cursor.

The syntax DEALLOCATE Cursor SQL is:

DEALLOCATE cursor_name

As previously discussed, a Cursor in SQL follows a lifecycle that comprises several steps. We have already described the basic syntax for each step. Now, let's see an SQL Server Cursor example with actual data.

Let's say we have a table named 'employees' with the following data:

We will use a SQL Cursor to increment each employee's salary by 10%.

DECLARE @id INT, @name VARCHAR(50), @salary INT;

DECLARE salary_cursor CURSOR FOR
SELECT id, name, salary
FROM employees;

OPEN salary_cursor;

FETCH NEXT FROM salary_cursor
INTO @id, @name, @salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @salary = @salary * 1.1;

    UPDATE employees
    SET salary = @salary
    WHERE CURRENT OF salary_cursor;

    FETCH NEXT FROM salary_cursor
    INTO @id, @name, @salary;
END;

CLOSE salary_cursor;
DEALLOCATE salary_cursor;

In this SQL Server Cursor example, we first declare variables to hold the values that will be fetched from the cursor. Then we declare the cursor “salary_cursor” and define a SELECT statement to fetch rows from the “employees” table. After that, we open the cursor and fetch the first row, and put the values into the variables we declared at the start.

The WHILE loop continues fetching the next row and updating the salary until all rows are processed. The “WHERE CURRENT OF” clause points to the most recently fetched row of the cursor. Finally, the cursor is closed and deallocated.

The result of the above SQL Server CURSOR would be:

Apart from the SQL Server Cursor, another method to perform row-by-row operations is to use the WHILE Loop. The WHILE Loop in SQL Server allows the repeated execution of a statement block until a specified condition is met.

Here is the same operation performed with a WHILE loop and without using a SQL Server Cursor:

DECLARE @id INT = 1;

WHILE @id <= (SELECT MAX(id) FROM employees)
BEGIN
    UPDATE employees
    SET salary = salary * 1.1
    WHERE id = @id;

    SET @id = @id + 1;
END;

Output:

In this example, the WHILE loop is used to iterate through the employee id's one at a time and update the corresponding salary. This is also an alternative to using a Cursor in SQL Server. But remember to use this example we must have an “id” column with unique values like a primary key.