The following
example consists of an outer cursor for sales orders and an
inner cursor for the detail of each order. It is an example for MS SQL nested cursor WHILE loops.
Cursor solutions do not scale well for large data sets. For such cases, it is better to use set-based operations. In the example below, the set-based solution is 7 times faster.
USE AdventureWorks2008;
GO
-- SQL Server Nested Cursors example - Execution timing setup
DBCC DROPCLEANBUFFERS
DECLARE @StartTime datetime = getdate()
-- Setup local variables
DECLARE @IterationID INT,
@OrderDetail VARCHAR(max),
@ProductName VARCHAR(10)
-- Setup table variable
DECLARE @Result TABLE (SalesOrderID INT, OrderDetail VARCHAR(max))
-- OUTER CURSOR declaration
DECLARE curOrdersForReport CURSOR FOR
SELECT SalesOrderID
FROM Sales.SalesOrderHeader
WHERE Year(OrderDate) = 2004
AND Month(OrderDate) between 2 and 4
ORDER BY SalesOrderID
OPEN curOrdersForReport
FETCH NEXT FROM curOrdersForReport INTO @IterationID
PRINT 'OUTER LOOP START'
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @OrderDetail = ''
-- INNER CURSOR declaration
DECLARE curDetailList CURSOR FOR
SELECT p.ProductNumber
FROM Sales.SalesOrderDetail pd
INNER JOIN Production.Product p
ON pd.ProductID = p.ProductID
WHERE pd.SalesOrderID = @IterationID
ORDER BY SalesOrderDetailID
OPEN curDetailList
FETCH NEXT FROM curDetailList INTO @ProductName
PRINT 'INNER LOOP START'
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @OrderDetail += @ProductName + ', '
FETCH NEXT FROM curDetailList INTO @ProductName
PRINT 'INNER LOOP'
END -- inner while
CLOSE curDetailList
DEALLOCATE curDetailList
-- Truncate trailing comma
SET @OrderDetail = left(@OrderDetail, len(@OrderDetail)-1)
INSERT INTO @Result VALUES (@IterationID, @OrderDetail)
FETCH NEXT FROM curOrdersForReport INTO @IterationID
PRINT 'OUTER LOOP'
END -- outer while
CLOSE curOrdersForReport
DEALLOCATE curOrdersForReport
-- Publish results
SELECT * FROM @Result ORDER BY SalesOrderID
-- Timing result
SELECT ExecutionMsec = datediff(millisecond, @StartTime, getdate())
GO
-- 2800 msecs
--------------------------------------------------------------------
-- Equivalent set-based operations solution
--------------------------------------------------------------------
-- Execution timing setup
DBCC DROPCLEANBUFFERS
DECLARE @StartTime datetime = getdate()
SELECT
poh.SalesOrderID,
OrderDetail = Stuff((
SELECT ', ' + ProductNumber as [text()]
FROM Sales.SalesOrderDetail pod
INNER JOIN Production.Product p
ON pod.ProductID = p.ProductID
WHERE pod.SalesOrderID = poh.SalesOrderID
ORDER BY SalesOrderDetailID
FOR XML PATH ('')), 1, 1, '')
FROM Sales.SalesOrderHeader poh
WHERE Year(OrderDate) = 2004
AND Month(OrderDate) between 2 and 4
ORDER BY SalesOrderID ;
-- Timing result
SELECT ExecutionMsec = datediff(millisecond, @StartTime, getdate())
GO
-- 400 msecs
/* Partial results
SalesOrderID OrderDetail
63119 FR-M63S-40
63120 SH-W890-S, SH-W890-M, SH-W890-L
63121 SH-W890-S, SH-W890-M
63122 PD-R347, HL-U509-B, HL-U509
*/
|
