DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to architect nested cursors?

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

*/

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE