http://sqlusa.com/bestpractices2005/doublecursor/
http://zlika.org/index.php/2010/03/06/sql-cursor-example/
------------
The following example uses @@FETCH_STATUS to control the WHILE loop in a typical cursor application:
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:
http://zlika.org/index.php/2010/03/06/sql-cursor-example/
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 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
*/
*/
-- 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
*/
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
...
*/
No comments:
Post a Comment