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:
| OrderDate |
PurchaseOrderID |
OrderDetail |
| July 1, 2002 |
157 |
HJ-3416, HJ-3816, HJ-3824, HJ-5161, HJ-5162, HJ-5811, |
| July 1, 2002 |
158 |
BA-8327, |
| July 1, 2002 |
159 |
AR-5381, |
| July 1, 2002 |
160 |
HJ-3816, HJ-3824, HJ-5161, |
| July 1, 2002 |
161 |
SP-2981, |
| July 1, 2002 |
162 |
BE-2908, |
| July 1, 2002 |
163 |
RM-R800, |
| July 1, 2002 |
164 |
RM-T801, |
| July 1, 2002 |
165 |
CA-5965, CA-6738, CA-7457, |
| July 1, 2002 |
166 |
LI-1201, LI-1400, LI-3800, LI-5160, |
| July 1, 2002 |
167 |
LJ-5811, LJ-5818, LJ-7161, LJ-7162, LJ-9080, LJ-9161, |
| July 1, 2002 |
168 |
CB-2903, CN-6137, CR-7833, |
| July 1, 2002 |
169 |
LN-3410, LN-3416, LN-3816, LN-3824, LN-4400, |
| July 1, 2002 |
170 |
PD-T852, |
| July 1, 2002 |
171 |
CR-7833, |
| July 1, 2002 |
172 |
RA-2345, |
| July 13, 2002 |
173 |
PB-6109, |
| July 13, 2002 |
174 |
CR-9981, |
| July 13, 2002 |
175 |
SD-2342, SD-9872, |
| July 13, 2002 |
176 |
PA-187B, PA-361R, PA-529S, PA-632U, |
| July 24, 2002 |
177 |
SE-M236, SE-M798, SE-M940, SE-R581, SE-R908, SE-R995, |
| July 24, 2002 |
178 |
RF-9198, |
| July 24, 2002 |
179 |
FC-3982, FL-2301, RC-0291, |
| July 24, 2002 |
180 |
RM-M464, RM-M692, |
| July 24, 2002 |
181 |
TP-0923, |
| July 24, 2002 |
182 |
FC-3982, FL-2301, |
| July 24, 2002 |
183 |
RM-M464, RM-M692, |
| July 24, 2002 |
184 |
NI-9522, |
| July 24, 2002 |
185 |
FW-1000, FW-1200, FW-1400, FW-3400, FW-3800, FW-5160, FW-5800, FW-7160, FW-9160, |
| July 24, 2002 |
186 |
PD-M282, PD-M340, |
| July 24, 2002 |
187 |
HN-3824, HN-4402, HN-5161, HN-5162, HN-5400, HN-5811, |
| July 24, 2002 |
188 |
MS-1981, MS-2259, MS-2341, MS-2348, MS-6061, |
| July 24, 2002 |
189 |
KW-4091, |
| July 24, 2002 |
190 |
RM-R436, RM-R600, RM-R800, |
| July 24, 2002 |
191 |
LE-5160, LE-6000, SE-T312, SE-T762, |
| July 24, 2002 |
192 |
SH-4562, |
| July 27, 2002 |
193 |
SH-9312, |
| July 27, 2002 |
194 |
SE-M236, SE-M798, |
| July 27, 2002 |
195 |
GT-0820, GT-1209, |
| July 27, 2002 |
196 |
PD-M282, PD-M340, |
| July 27, 2002 |
197 |
SD-9872, |
| July 27, 2002 |
198 |
SE-R581, SE-R908, |
| July 27, 2002 |
199 |
SE-M940, |
| July 27, 2002 |
200 |
PD-M562, |
|
 |
| The World Leader
in SQL Server Training |
|