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 ‘TSQL 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 _