SQL Cursor Example

A SQL Server cursor is a database object that enables the iterative processing of data. Cursors are commonly used in scenarios where a set-based approach to data manipulation is not feasible or efficient, such as when performing complex data manipulations or implementing complex business logic.

Consider the following example, where we have a table named “Employees” that contains information about employees of a company:

CREATE TABLE Employees (
EmployeeID int PRIMARY KEY,
FirstName varchar(50),
LastName varchar(50),
HireDate date,
Salary decimal(10,2)
);

Suppose we want to calculate the average salary of all employees hired before a certain date. We can achieve this using a cursor as follows:

DECLARE @employeeID int, @firstName varchar(50), @lastName varchar(50), @hireDate date, @salary decimal(10,2);
DECLARE @totalSalary decimal(10,2) = 0, @numEmployees int = 0, @averageSalary decimal(10,2);

DECLARE employeeCursor CURSOR FOR
SELECT EmployeeID, FirstName, LastName, HireDate, Salary
FROM Employees
WHERE HireDate < '2022-01-01';

OPEN employeeCursor;

FETCH NEXT FROM employeeCursor INTO @employeeID, @firstName, @lastName, @hireDate, @salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @totalSalary = @totalSalary + @salary;
    SET @numEmployees = @numEmployees + 1;

    FETCH NEXT FROM employeeCursor INTO @employeeID, @firstName, @lastName, @hireDate, @salary;
END;

CLOSE employeeCursor;
DEALLOCATE employeeCursor;

IF @numEmployees > 0
BEGIN
    SET @averageSalary = @totalSalary / @numEmployees;
    SELECT @averageSalary AS AverageSalary;
END
ELSE
BEGIN
    SELECT 'No employees found.' AS AverageSalary;
END

In this example, we declare a cursor named “employeeCursor” that selects all employees hired before January 1, 2022. We then open the cursor and fetch the first row into variables that correspond to the columns of the “Employees” table. We then enter a loop that iterates through all the rows returned by the cursor, incrementing a total salary and employee count variable for each row. Finally, we close and deallocate the cursor, and calculate and display the average salary of the employees that were selected.

Cursors can be powerful tools for processing data in SQL Server, but they should be used judiciously and with caution, as they can have performance implications and may not scale well with large datasets. It is often better to try to use a set-based approach before resorting to cursors.

SQL cursor example more information

Contact us at:

p: 1300 088 712

e:info@challengerx.com.au

_{You probably found us with: web & database designers, web and database designers, SQL design melbourne, SQL designer melbourne}

ChallengerX