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:

-- T-SQL cursor declaration

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

    PRINT 'Cursor loop'

    FETCH NEXT FROM curManager;

  END; -- while

CLOSE curManager;

DEALLOCATE curManager;

GO

/* Partial results

EmployeeID        Title

3                 Engineering Manager

EmployeeID        Title

6                 Marketing Manager

*/

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 cursors T-SQL script:

-- MSSQL nested cursors

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

/* Messages (partial)

OUTER LOOP

MIDDLE LOOP

INNER LOOP

INNER LOOP

INNER LOOP

...

*/

Here is the result set: