Can I loop through a table variable in T-SQL?

Solution 1:

Add an identity to your table variable, and do an easy loop from 1 to the @@ROWCOUNT of the INSERT-SELECT.

Try this:

DECLARE @RowsToProcess  int
DECLARE @CurrentRow     int
DECLARE @SelectCol1     int

DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), col1 int )  
INSERT into @table1 (col1) SELECT col1 FROM table2
SET @RowsToProcess=@@ROWCOUNT

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
    SET @CurrentRow=@CurrentRow+1
    SELECT 
        @SelectCol1=col1
        FROM @table1
        WHERE RowID=@CurrentRow

    --do your thing here--

END

Solution 2:

DECLARE @table1 TABLE (
    idx int identity(1,1),
    col1 int )

DECLARE @counter int

SET @counter = 1

WHILE(@counter < SELECT MAX(idx) FROM @table1)
BEGIN
    DECLARE @colVar INT

    SELECT @colVar = col1 FROM @table1 WHERE idx = @counter

    -- Do your work here

    SET @counter = @counter + 1
END

Believe it or not, this is actually more efficient and performant than using a cursor.

Solution 3:

My two cents.. From KM.'s answer, if you want to drop one variable, you can do a countdown on @RowsToProcess instead of counting up.

DECLARE @RowsToProcess  int;

DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), col1 int )  
INSERT into @table1 (col1) SELECT col1 FROM table2
SET @RowsToProcess = @@ROWCOUNT 

WHILE @RowsToProcess > 0 -- Countdown
BEGIN
    SELECT *
        FROM @table1
        WHERE RowID=@RowsToProcess

    --do your thing here--

    SET @RowsToProcess = @RowsToProcess - 1; -- Countdown
END

Solution 4:

You can loop through the table variable or you can cursor through it. This is what we usually call a RBAR - pronounced Reebar and means Row-By-Agonizing-Row.

I would suggest finding a SET-BASED answer to your question (we can help with that) and move away from rbars as much as possible.

Solution 5:

look like this demo:

DECLARE @vTable TABLE (IdRow int not null primary key identity(1,1),ValueRow int);

-------Initialize---------
insert into @vTable select 345;
insert into @vTable select 795;
insert into @vTable select 565;
---------------------------

DECLARE @cnt int = 1;
DECLARE @max int = (SELECT MAX(IdRow) FROM @vTable);

WHILE @cnt <= @max
BEGIN
    DECLARE @tempValueRow int = (Select ValueRow FROM @vTable WHERE IdRow = @cnt);

    ---work demo----
    print '@tempValueRow:' + convert(varchar(10),@tempValueRow);
    print '@cnt:' + convert(varchar(10),@cnt);
    print'';
    --------------

    set @cnt = @cnt+1;
END

Version without idRow, using ROW_NUMBER

    DECLARE @vTable TABLE (ValueRow int);
-------Initialize---------
insert into @vTable select 345;
insert into @vTable select 795;
insert into @vTable select 565;
---------------------------

DECLARE @cnt int = 1;
DECLARE @max int = (select count(*) from @vTable);

WHILE @cnt <= @max
BEGIN
    DECLARE @tempValueRow int = (
        select ValueRow 
        from (select ValueRow
            , ROW_NUMBER() OVER(ORDER BY (select 1)) as RowId 
            from @vTable
        ) T1 
    where t1.RowId = @cnt
    );

    ---work demo----
    print '@tempValueRow:' + convert(varchar(10),@tempValueRow);
    print '@cnt:' + convert(varchar(10),@cnt);
    print'';
    --------------

    set @cnt = @cnt+1;
END