DB Cursor

A cursor in SQL Server is a database object that allows us to retrieve each row at a time and manipulate its data. A cursor is nothing more than a pointer to a row. It's always used in conjunction with a SELECT statement. It is usually a collection of SQL logic that loops through a predetermined number of rows one by one. A simple illustration of the cursor is when we have an extensive database of worker's records and want to calculate each worker's salary after deducting taxes and leaves.

The SQL Server cursor's purpose is to update the data row by row, change it, or perform calculations that are not possible when we retrieve all records at once. It's also useful for performing administrative tasks like SQL Server database backups in sequential order. Cursors are mainly used in the development, DBA, and ETL processes.

The basic structure of a cursor is as follows:

1. Declare the cursor
2. Open the cursor and execute the SELECT statement
3. Fetch the first row
4. While there are more rows, process the current row and fetch the next row
5. Close the cursor and deallocate resources

An example of a cursor in Microsoft SQL Server

DECLARE @employee_id INT
DECLARE @employee_name VARCHAR(50)
DECLARE employee_cursor CURSOR FOR
SELECT employee_id, employee_name
FROM employees

OPEN employee_cursor
FETCH NEXT FROM employee_cursor INTO @employee_id, @employee_name

WHILE @@FETCH_STATUS = 0
BEGIN
        PRINT 'Employee ID: ' + CONVERT(VARCHAR(10), @employee_id) + ' Employee Name: ' + @employee_name
        FETCH NEXT FROM employee_cursor INTO @employee_id, @employee_name
END

CLOSE employee_cursor
DEALLOCATE employee_cursor

The MS SQL Server cursor attributes article