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

Question

Is there anyway to loop through a table variable in T-SQL?

DECLARE @table1 TABLE ( col1 int )  
INSERT into @table1 SELECT col1 FROM table2

I use cursors as well, but cursors seem less flexible than table variables.

DECLARE cursor1 CURSOR  
    FOR SELECT col1 FROM table2  
OPEN cursor1  
FETCH NEXT FROM cursor1

I would like to be able to use a table variable in the same manner as a cursor. That way I could execute some query on the table variable in one part of the procedure, and then later execute some code for each row in the table variable.

Any help is greatly appreciated.

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

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

Solution 5

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.

View more solutions

Comments

[KM.]

why select the max each time in the loop?

[KM.]

each iteration you hit the table variable 3 times, which can't be that efficient

[Justin Niessner]

You could select it once and store it in a variable easily enough...this was just a few keystrokes shorter.

[KM.]

why select the max each time in the loop? As a result, you have to hit the table variable two times per iteration. You could remove the SELECT MAX() in the WHILE() if you capture the @@ROWCOUNT from the table varaible population, like I do in my answer.

[Kuyenda]

This is actually why I want to use a table variable instead of a cursor. I generally look for a way to get my intended result using a JOIN on a table variable, but if I can't find a way to use a JOIN, then I can fall back on a loop on that same table variable. But I agree, set-based is best.

[Kuyenda]

This seems like the simplest of the lot. Thanks!

[KM.]

your code: DECLARE cursor1 CURSOR FOR @table1 OPEN cursor1 will not work. Cursors have to have a SELECT in their definition, like your second code example. If you do some tests, you will find that looping without using a cursor is faster than looping using a cursor.

[George Mastros]

Looping on a table variable is no better than a cursor. In fact, it can actually be worse. The only real benefit of changing code from cursors to loops is "bragging rights". Ex: "I don't have any cursors in my code".

[beerwin]

This is a better solution as the accepted answer as it doesn't depend on the content of the table variable.