- 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'

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

------------
-- 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: