T-SQL Nested Cursor in SQL Server 2008
SQL developers can create nested cursor in SQL Server by defining an outer cursor and within the cursor code a new cursor is defined for each row in main cursor select. The inner cursor is created, executed, closed and deallocated each time in the outer cursor for each row.
In short, a nested cursor is a defining a loop in another loop in SQL Server.
SQL Server administrators and t-sql developers can use nested cursors for complex structures. But when considering performance issues even using a sql cursor is not suggested, a nested cursor is not the first solution that will be choosen.
I want to give a nested cursor sample t-sql code which uses AdventureWorks2008 sample database. This nested sql cursor sample will loop through all departments in Department table in AdventureWorks2008 sql database and list all employee working in that department using the Employee and EmployeeDepartmentHistory tables.
DECLARE
@DepartmentID int, @DepartmentName nvarchar(50),
@PersonID int, @FirstName nvarchar(50), @LastName nvarchar(50)
DECLARE department_cursor CURSOR FOR
SELECT DepartmentID, Name FROM HumanResources.Department
OPEN department_cursor;
FETCH NEXT FROM department_cursor INTO @DepartmentID, @DepartmentName;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE employee_cursor CURSOR FOR
SELECT
E.BusinessEntityID, P.FirstName, P.LastName
FROM HumanResources.EmployeeDepartmentHistory H
INNER JOIN HumanResources.Employee E ON E.BusinessEntityID = H.BusinessEntityID
INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID
WHERE
H.EndDate IS NULL
AND H.DepartmentID = @DepartmentID
OPEN employee_cursor;
FETCH NEXT FROM employee_cursor INTO @PersonID, @FirstName, @LastName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT
CAST(@DepartmentID as varchar(10)) + ' ' +
@DepartmentName + ' ' +
CAST(@PersonID as varchar(10)) + ' ' +
@FirstName + ' ' + @LastName
FETCH NEXT FROM employee_cursor INTO @PersonID, @FirstName, @LastName
END;
CLOSE employee_cursor;
DEALLOCATE employee_cursor;
FETCH NEXT FROM department_cursor INTO @DepartmentID, @DepartmentName;
END
CLOSE department_cursor;
DEALLOCATE department_cursor;
The output of the above SQL Server nested cursor will be as follows:
SQL developers should always keep that in mind, if there is a better way to solve the t-sql problem without using sql cursors that method should be choosen. For example the above list can also be created using the following transact-sql select statement.
select
D.DepartmentID, D.Name, E.BusinessEntityID, P.FirstName, P.LastName
from HumanResources.Department D
left join HumanResources.EmployeeDepartmentHistory ED ON D.DepartmentID = ED.DepartmentID
left join HumanResources.Employee E ON E.BusinessEntityID = ED.BusinessEntityID
left join Person.Person P ON P.BusinessEntityID = E.BusinessEntityID
where ED.EndDate is null
order by D.DepartmentID
For more about SQL cursor samples and SQL Server cursor codes, please check the following sql tutorials:
List Count of Rows in All Tables in Database using SQL Server Cursor
Transact-SQL Cursor Sample Code
T-SQL Nested Cursor Sample in SQL Server 2008