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