SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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;
Code

The output of the above SQL Server nested cursor will be as follows:

TSQL nested cursor in SQL Server 2008

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
Code

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



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.