Pages

Monday, April 28, 2014

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
*/
 ------------

No comments:

Post a Comment