DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to architect a WHILE loop using table variable?

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

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE