Triple Nested Cursors

By Kalman Toth, Business Intelligence Architect

October 21, 2007

The following example uses @@FETCH_STATUS to control the WHILE loop in a typical cursor application:

DECLARE curManager CURSOR FOR
SELECT EmployeeID, Title FROM AdventureWorks.HumanResources.Employee
WHERE Title like '%manager%' OR Title like '%super%';
OPEN curManager;
FETCH NEXT FROM curManager;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM curManager;
END;
CLOSE curManager;
DEALLOCATE curManager;
GO

However, the @@FETCH_STATUS is global to all cursors on a connection, therefore using @@FETCH_STATUS to control nested cursors may not be advisable. To play it safe for the case of following triple nested cursors demonstration, we avoid using @@FETCH_STATUS. Instead we order the SELECTs for the cursor and find the max value on one unique column. We use a comparison between the running values and maximum value to control the loop. The OUTER cursor loop is based on OrderDate. The MIDDLE cursor loop is based PurchaseOrderID-s received on a particular date. The INNER cursor loop is based on the products belonging to a particular PurchaseOrderID.

This is the entire triple nested cursor query:

USE AdventureWorks
GO
DECLARE @DateIteration datetime, @IterationID INT, @OrderDetail VARCHAR(1024), @ProductNo VARCHAR(10)
DECLARE @MaxOrderDate datetime, @MaxPOID int, @MaxProdNo VARCHAR(10)
DECLARE @Result TABLE (OrderDate datetime, PurchaseOrderID INT, OrderDetail VARCHAR(1024))

DECLARE curOrderDate CURSOR FOR
SELECT DISTINCT OrderDate FROM Purchasing.PurchaseOrderHeader
WHERE year(OrderDate)=2002 and month(OrderDate)=7
ORDER BY OrderDate

SELECT @MaxOrderDate=OrderDate FROM Purchasing.PurchaseOrderHeader
WHERE year(OrderDate)=2002 and month(OrderDate)=7

OPEN curOrderDate
FETCH NEXT FROM curOrderDate INTO @DateIteration
PRINT 'OUTER LOOP'
WHILE ( 1 < 2)
BEGIN

DECLARE curOrdersForReport CURSOR FOR
SELECT PurchaseOrderID FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate=@DateIteration
ORDER BY PurchaseOrderID

SELECT @MaxPOID = PurchaseOrderID FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate=@DateIteration

OPEN curOrdersForReport
FETCH NEXT FROM curOrdersForReport INTO @IterationID
PRINT 'MIDDLE LOOP'
WHILE (1 < 2)
BEGIN
SET @OrderDetail = ''

DECLARE curDetailList CURSOR FOR
SELECT p.ProductNumber
FROM Purchasing.PurchaseOrderDetail pd
INNER JOIN Production.Product p
ON pd.ProductID = p.ProductID
WHERE pd.PurchaseOrderID = @IterationID
ORDER BY p.ProductNumber

SELECT @MaxProdNo=p.ProductNumber
FROM Purchasing.PurchaseOrderDetail pd
INNER JOIN Production.Product p
ON pd.ProductID = p.ProductID
WHERE pd.PurchaseOrderID = @IterationID

OPEN curDetailList
FETCH NEXT FROM curDetailList INTO @ProductNo
PRINT 'INNER LOOP'
WHILE (1 < 2)
BEGIN
SET @OrderDetail = @OrderDetail + @ProductNo + ', '
IF (@ProductNo = @MaxProdNo) BREAK
FETCH NEXT FROM curDetailList INTO @ProductNo
PRINT 'INNER LOOP'
END
CLOSE curDetailList
DEALLOCATE curDetailList
INSERT INTO @Result VALUES (@DateIteration, @IterationID, @OrderDetail)
IF (@IterationID = @MaxPOID ) BREAK
FETCH NEXT FROM curOrdersForReport INTO @IterationID
PRINT 'MIDDLE LOOP'
END
CLOSE curOrdersForReport
DEALLOCATE curOrdersForReport

IF (@DateIteration = @MaxOrderDate ) BREAK
FETCH NEXT FROM curOrderDate INTO @DateIteration
PRINT 'OUTER LOOP'
END
CLOSE curOrderDate
DEALLOCATE curOrderDate

SELECT * FROM @Result
GO

Here is the result set: