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.
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 Cursor Syntax - cursor loop syntax - using t-sql cursor
------------
USE AdventureWorks2008;
DECLARE curSubcategory CURSOR READ_ONLY -- 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 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 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 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
*/
|
