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 Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.




download SQL Server 2014



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:

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

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





Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers










Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems