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 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to construct nested cursors?

Execute the following Microsoft SQL Server T-SQL example scripts in Management Studio Query Editor to demonstrate the construction of cursor and nested cursors logic.

SQL Server Cursor Performance: Cursor solutions do not scale well for large data sets. For such cases, it is better to use set-based operations. See set-based solution T-SQL script following the Purchase Order nested cursors demo.

-- SQL Server cursor example - row-by-row operation - DECLARE CURSOR

DECLARE @dbName sysname

DECLARE AllDBCursor CURSOR  STATIC LOCAL FOR

  SELECT   name FROM     MASTER.dbo.sysdatabases

  WHERE    name NOT IN ('master','tempdb','model','msdb') ORDER BY name

OPEN AllDBCursor; FETCH  AllDBCursor INTO @dbName;

WHILE (@@FETCH_STATUS = 0) -- loop through all db-s 

  BEGIN

/***** PROCESSING (like BACKUP) db by db goes here - record-by-record process  *****/ 

    PRINT @dbName

    FETCH  AllDBCursor   INTO @dbName

  END -- while 

CLOSE AllDBCursor; DEALLOCATE AllDBCursor;

/* Messages

AdventureWorks

AdventureWorks2008

AdventureWorksDW

AdventureWorksDW2008

..... */

------------

-- T-SQL Cursor declaration and usage example - cursor loop syntax - using t-sql cursor

------------

USE AdventureWorks2008;

DECLARE curSubcategory CURSOR STATIC LOCAL               -- sql declare cursor

FOR SELECT ProductSubcategoryID, Subcategory=Name

FROM Production.ProductSubcategory ORDER BY Subcategory

DECLARE @Subcategory varchar(40), @PSID int

OPEN curSubcategory

FETCH NEXT FROM curSubcategory INTO @PSID, @Subcategory  -- sql fetch cursor

WHILE (@@fetch_status = 0)                    -- sql cursor fetch_status

BEGIN -- begin cursor loop

/***** USER DEFINED CODE HERE - POSSIBLY NESTED CURSOR *****/

            DECLARE @Msg varchar(128)

            SELECT @Msg = 'ProductSubcategory info: ' + @Subcategory + ' '+

                   CONVERT(varchar,@PSID)

            PRINT @Msg

FETCH NEXT FROM curSubcategory INTO @PSID, @Subcategory   -- sql fetch cursor

END -- end cursor loop

CLOSE curSubcategory

DEALLOCATE curSubcategory

GO

/* Partial output in Messages

 

ProductSubcategory info: Bib-Shorts 18

ProductSubcategory info: Bike Racks 26

ProductSubcategory info: Bike Stands 27

ProductSubcategory info: Bottles and Cages 28

ProductSubcategory info: Bottom Brackets 5

ProductSubcategory info: Brakes 6

*/

------------


------------

-- T SQL Search All Text & XML Columns in All Tables

------------

-- SQL nested cursors - sql server nested cursor - transact sql nested cursor

USE AdventureWorks;

GO

-- SQL Server create stored procedure with nested cursors

CREATE PROC sprocSearchKeywordInAllTables  @Keyword NVARCHAR(64)

AS

  BEGIN

    SET NOCOUNT  ON

    DECLARE  @OutputLength VARCHAR(4),

             @NolockOption CHAR(8)

         SET @OutputLength = '256'

         SET @NolockOption = ''

         -- SET @NolockOption =  '(NOLOCK)'

    DECLARE  @DynamicSQL   NVARCHAR(MAX),

             @SchemaTableName   NVARCHAR(256),

             @SchemaTableColumn NVARCHAR(128),

             @SearchWildcard    NVARCHAR(128)

         SET @SearchWildcard = QUOTENAME('%' + @Keyword + '%',CHAR(39)+CHAR(39))

         PRINT @SearchWildcard

    DECLARE  @SearchResults  TABLE(

                                   SchemaTableColumn NVARCHAR(384),

                                   TextWithKeyword   NVARCHAR(MAX)

                                   )

 

    DECLARE curAllTables CURSOR  STATIC LOCAL FOR

    SELECT   QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) AS ST

    FROM     INFORMATION_SCHEMA.TABLES

    WHERE    TABLE_TYPE = 'BASE TABLE'

             AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.'

             + QUOTENAME(TABLE_NAME)), 'IsMSShipped') != 1

    ORDER BY ST

    OPEN curAllTables

    FETCH NEXT FROM curAllTables

    INTO @SchemaTableName

    

    WHILE (@@FETCH_STATUS = 0) -- Outer cursor loop

      BEGIN

        PRINT @SchemaTableName

        SET @SchemaTableColumn = ''

        DECLARE curAllColumns CURSOR  FOR -- Nested cursor

        SELECT   QUOTENAME(COLUMN_NAME)

        FROM     INFORMATION_SCHEMA.COLUMNS

        WHERE    TABLE_NAME = PARSENAME(@SchemaTableName,1)

                 AND TABLE_SCHEMA = PARSENAME(@SchemaTableName,2)

                 AND DATA_TYPE IN ('varchar','nvarchar','char','nchar','xml')

        ORDER BY ORDINAL_POSITION

        OPEN curAllColumns

        FETCH NEXT FROM curAllColumns

        INTO @SchemaTableColumn

        WHILE (@@FETCH_STATUS = 0) -- Inner cursor loop (nested cursor while)

          BEGIN

            PRINT '  ' + @SchemaTableColumn

            SET @DynamicSQL = 'SELECT ''' + @SchemaTableName + '.' +

              @SchemaTableColumn + ''', LEFT(CONVERT(nvarchar(max),' +

              @SchemaTableColumn + '),' + @OutputLength + ')  FROM ' +

              @SchemaTableName + ' '+@NolockOption+

              ' WHERE CONVERT(nvarchar(max),' + @SchemaTableColumn +

              ') LIKE ' + @SearchWildcard

            INSERT INTO @SearchResults

            EXEC sp_executeSQL  @DynamicSQL

            FETCH NEXT FROM curAllColumns

            INTO @SchemaTableColumn

          END  -- Inner cursor loop

        CLOSE curAllColumns

        DEALLOCATE curAllColumns

        FETCH NEXT FROM curAllTables

        INTO @SchemaTableName

      END  -- Outer cursor loop

    CLOSE curAllTables

    DEALLOCATE curAllTables

   

    SELECT DISTINCT SchemaTableColumn, TextWithKeyWord FROM   @SearchResults

  END

GO

 

EXEC sprocSearchKeywordInAllTables  'Hamilton'

EXEC sprocSearchKeywordInAllTables  'Sánchez'

EXEC sprocSearchKeywordInAllTables  'O''Donnell'

EXEC sprocSearchKeywordInAllTables  'Certification'

------------

The following nested cursors consist of an outer cursor for purchase orders header info and an inner cursor for the details of each purchase order. It is an example for MS SQL nested cursor loop.

------------
-- SQL Server Nested Cursors example - transact sql nested cursor

------------

-- SQL nested cursors - transact sql fetch_status - transact sql while loop

-- SQL nesting cursors - transact sql fetch next

-- T-SQL script for execution timing setup

USE AdventureWorks;

DBCC DROPCLEANBUFFERS

DECLARE @StartTime datetime

SET @StartTime = getdate()

 

-- Setup local variables

DECLARE     @IterationID INT,

            @OrderDetail VARCHAR(max),

            @ProductName VARCHAR(10)

 

-- Setup table variable

DECLARE @Result TABLE (PurchaseOrderID INT, OrderDetail VARCHAR(max))

 

 

-- OUTER CURSOR declaration - transact sql declare cursor

DECLARE curOrdersForReport CURSOR STATIC LOCAL FOR

SELECT PurchaseOrderID

FROM Purchasing.PurchaseOrderHeader

WHERE Year(OrderDate) = 2004

  AND Month(OrderDate) = 2

ORDER BY PurchaseOrderID

 

OPEN curOrdersForReport

FETCH NEXT FROM curOrdersForReport INTO @IterationID

PRINT 'OUTER LOOP START'

 

WHILE (@@FETCH_STATUS = 0) -- sql cursor fetch_status

BEGIN

      SET @OrderDetail = ''

 

-- INNER CURSOR declaration - transact sql declare cursor

-- SQL Nested Cursor - sql cursor nested - cursor nesting

 

      DECLARE curDetailList CURSOR STATIC LOCAL FOR

      SELECT p.productNumber

      FROM Purchasing.PurchaseOrderDetail pd

      INNER JOIN Production.Product p

      ON pd.ProductID = p.ProductID

      WHERE pd.PurchaseOrderID = @IterationID

      ORDER BY PurchaseOrderDetailID

 

      OPEN curDetailList

      FETCH NEXT FROM curDetailList INTO @ProductName

      PRINT 'INNER LOOP START'

 

      WHILE (@@FETCH_STATUS = 0)

      BEGIN

            SET @OrderDetail = @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 PurchaseOrderID

 

-- Timing result

SELECT ExecutionMsec = datediff(millisecond, @StartTime, getdate())

GO

-- 220 msecs

 

------------

-- Equivalent set-based operations solution

------------

 

-- Execution timing setup

DBCC DROPCLEANBUFFERS

DECLARE @StartTime datetime

SET @StartTime = getdate()

 

-- SQL comma-limited list generation

-- SQL nested select statement

-- SQL FOR XML PATH

SELECT

      poh.PurchaseOrderID,

      OrderDetail = Stuff((

-- SQL correlated subquery

      SELECT ', ' + ProductNumber as [text()]

      FROM Purchasing.PurchaseOrderDetail pod

      INNER JOIN Production.Product p

      ON pod.ProductID = p.ProductID

      WHERE pod.PurchaseOrderID = poh.PurchaseOrderID

      ORDER BY PurchaseOrderDetailID

      FOR XML PATH ('')), 1, 1, '')

FROM Purchasing.PurchaseOrderHeader poh

WHERE Year(OrderDate) = 2004

  AND Month(OrderDate) = 2

ORDER BY PurchaseOrderID ;

 

-- Timing result

SELECT ExecutionMsec = datediff(millisecond, @StartTime, getdate())

GO

-- 110 msecs

 

 

/* Partial results

 

PurchaseOrderID   OrderDetail

1696              GT-0820, GT-1209

1697              HN-6320, HN-7161, HN-7162, HN-8320, HN-9161, HN-9168

1698              NI-4127

1699              RM-T801

1700              LI-1201, LI-1400, LI-3800

1701              TI-R982, TI-T723
*/

 

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:

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, 

 

Related link:

How to Perform SQL Server Row-by-Row Operations Without Cursors

Hugo Kornelis: Curious cursor optimization options

 

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