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,
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.
Contact us at:
p: 1300 088 712
You probably found us with: web & database designers, web and database designers, SQL design melbourne, SQL designer melbourne