|
Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate cursor-like WHILE loop construction.
------------
-- WARNING: WHILE (and cursor) loop does not scale well for large datasets
-- Use set-based operations for large datasets
------------
-- T-SQL create stored procedure - no parameter
-- MSSQL while loop - insert select - table variable
USE Northwind
GO
CREATE PROC SupplierStats
-- ALTER PROC SupplierStats
AS
BEGIN
SET nocount ON
DECLARE @imax INT,
@i INT
DECLARE @Contact VARCHAR(100),
@Company VARCHAR(50)
-- The RowID identity column will be used for loop control
-- MSSQL declare table variable
DECLARE @CompanyInfo TABLE(
RowID INT IDENTITY ( 1 , 1 ),
CompanyName VARCHAR(100),
ContactName VARCHAR(50)
)
INSERT @CompanyInfo
SELECT CompanyName,
ContactName
FROM Suppliers
WHERE ContactName LIKE '[a-k]%'
ORDER BY CompanyName
SET @imax = @@ROWCOUNT
SET @i = 1
WHILE (@i <= @imax)
BEGIN
SELECT @Contact = ContactName,
@Company = CompanyName
FROM @CompanyInfo
WHERE RowID = @i
------------------------------------------------------
-- INSERT PROCESSING HERE
------------------------------------------------------
PRINT CONVERT(varchar,@i)+' Contact: ' + @Contact + ' at ' + @Company
SET @i = @i + 1
END -- WHILE
END -- SPROC
GO
-- Stored procedure execution - test stored procedure
EXEC SupplierStats
GO
/* Partial results
1 Contact: Guylène Nodier at Aux joyeux ecclésiastiques
2 Contact: Cheryl Saylor at Bigfoot Breweries
3 Contact: Antonio del Valle Saavedra at Cooperativa de Quesos 'Las Cabras'
4 Contact: Charlotte Cooper at Exotic Liquids
5 Contact: Chantal Goulet at Forêts d'érables
6 Contact: Elio Rossi at Formaggi Fortini s.r.l.
7 Contact: Eliane Noz at Gai pâturage
8 Contact: Anne Heikkonen at Karkki Oy
*/
------------
Related articles:
http://sqlusa.com/bestpractices2005/doublecursor/
WHILE (Transact-SQL)
SQL Server Table Variables To Eliminate Cursors
|
