Untitled Document
 
Untitled Document
NEWS
Cursor in MSSQL
Source : Cursor in MSSQL
Post date : 21-08-2013

Cursor in MSSQL

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

OrderDatePurchaseOrderIDOrderDetail
July 1, 2002157HJ-3416, HJ-3816, HJ-3824, HJ-5161, HJ-5162, HJ-5811, 
July 1, 2002158BA-8327, 
July 1, 2002159AR-5381, 
July 1, 2002160HJ-3816, HJ-3824, HJ-5161, 
July 1, 2002161SP-2981, 
July 1, 2002162BE-2908, 
July 1, 2002163RM-R800, 
July 1, 2002164RM-T801, 
July 1, 2002165CA-5965, CA-6738, CA-7457, 
July 1, 2002166LI-1201, LI-1400, LI-3800, LI-5160, 
July 1, 2002167LJ-5811, LJ-5818, LJ-7161, LJ-7162, LJ-9080, LJ-9161, 
July 1, 2002168CB-2903, CN-6137, CR-7833, 
July 1, 2002169LN-3410, LN-3416, LN-3816, LN-3824, LN-4400, 
July 1, 2002170PD-T852, 
July 1, 2002171CR-7833, 
July 1, 2002172RA-2345, 
July 13, 2002173PB-6109, 
July 13, 2002174CR-9981, 
July 13, 2002175SD-2342, SD-9872, 
July 13, 2002176PA-187B, PA-361R, PA-529S, PA-632U, 
July 24, 2002177SE-M236, SE-M798, SE-M940, SE-R581, SE-R908, SE-R995, 
July 24, 2002178RF-9198, 
July 24, 2002179FC-3982, FL-2301, RC-0291, 
July 24, 2002180RM-M464, RM-M692, 
July 24, 2002181TP-0923, 
July 24, 2002182FC-3982, FL-2301, 
July 24, 2002183RM-M464, RM-M692, 
July 24, 2002184NI-9522, 
July 24, 2002185FW-1000, FW-1200, FW-1400, FW-3400, FW-3800, FW-5160, FW-5800, FW-7160, FW-9160, 
July 24, 2002186PD-M282, PD-M340, 
July 24, 2002187HN-3824, HN-4402, HN-5161, HN-5162, HN-5400, HN-5811, 
July 24, 2002188MS-1981, MS-2259, MS-2341, MS-2348, MS-6061, 
July 24, 2002189KW-4091, 
July 24, 2002190RM-R436, RM-R600, RM-R800, 
July 24, 2002191LE-5160, LE-6000, SE-T312, SE-T762, 
July 24, 2002192SH-4562, 
July 27, 2002193SH-9312, 
July 27, 2002194SE-M236, SE-M798, 
July 27, 2002195GT-0820, GT-1209, 
July 27, 2002196PD-M282, PD-M340, 
July 27, 2002197SD-9872, 
July 27, 2002198SE-R581, SE-R908, 
July 27, 2002199SE-M940, 
July 27, 2002200PD-M562, 

 

Untitled Document
 ARTIKEL / BERITA TERBARU
 PROJECT SEDANG BERJALAN
 Prima Supplier -AutoFarrel Tiban & Sei Panas
 Prima Laundry -Executive laundry sukajadi
 Prima Clinic -Klinik Dunia Medika, T.Uncang & Raja Medika, Batu Aji
 Prima Payroll -PT. Samchin Enginering, PT. Karya Sumber Daya, PT. Asiatec Recyclindo
 Prima Apotek -Apotek Penuin
 Prima POS -Keperasi Bosowa
Locations of visitors to this page

   
 
Untitled Document
YM SUPPORT     CS 1 :


Free counters!

copyright © 2013 PRIMA INFODATA , all right reserved | best view @ mozilla firefox | [ webmail ]