Array in SQL Server 2000

Just found a good article about table variable in T-SQL.

-- Transact SQL Cursor
CREATE PROC SQL_Cursor
AS
/*Local variables */
DECLARE @ContName VARCHAR(100),
@CompName VARCHAR(50)
/*create the cursor*/
DECLARE MyCursor CURSOR FOR
SELECT CompanyName, ContactName
FROM Suppliers
WHERE ContactName LIKE 'c%'
/*open the cursor*/
OPEN MyCursor
/*get row values*/
FETCH MyCursor INTO @CompName, @ContName
/*perform oterations with single row*/
PRINT 'T_SQL cursor row ' + @ContName + ' ' + @CompName
/*establish loop structure*/
WHILE @@FETCH_STATUS = 0
BEGIN
/*get row values*/
FETCH MyCursor INTO @CompName, @ContName
/*perform operations with single row*/
PRINT 'T_SQL cursor row ' + @ContName + ' ' + @CompName
END
/*close the cursor*/
CLOSE MyCursor
/*remove the cursor definition*/
DEALLOCATE MyCursor

-- Cursor Simulator
CREATE PROC CursorSimulator
AS
/*Prepare TABLE variable to take resultset*/
DECLARE @tbl TABLE(
RowID INT IDENTITY(1, 1),
CompanyName VARCHAR(100),
ContactName VARCHAR(50))
/*Local variables */
DECLARE @ContName VARCHAR(100),
@CompName VARCHAR(50),
@count int, /*create local @@fetch_status*/
@iRow int /*row pointer (index)*/
/* create array simulator */
INSERT @tbl
SELECT CompanyName, ContactName
FROM Suppliers
WHERE ContactName LIKE 'c%'
/*get array Upper Bound (highest ID number)*/
SET @count = @@ROWCOUNT
/*initialize index counter*/
SET @iRow = 1
/*establish loop structure*/
WHILE @iRow <= @count BEGIN /*get row values*/ SELECT @ContName = CompanyName, @CompName = ContactName FROM @tbl WHERE RowID = @iRow /*perform operations with single row*/ PRINT 'My cursor row ' + @ContName + ' ' + @CompName /*go to next row*/ SET @iRow = @iRow + 1 END