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.

Chalutz

similar question here: #62467 – Nudd

"cursors seem less flexible than table variables". This statement doesn't really make sense. They are entirely different things. You can certainly use a cursor to iterate through a table variable. – Contrition

Does this answer your question? Is there a way to loop through a table variable in TSQL without using a cursor? – Untread

Answer 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

Dying

This seems like the simplest of the lot. Thanks! – Chalutz

Answer 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.

Colophon

why select the max each time in the loop? – Dying

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

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. – Dying

This omits the last entry from the table because counter starts at 1, also the SELECT MAX(idx)... statement should be in parenthesis. – Polypary

Answer 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

Rewire

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

Answer 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.

Agenesis

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. – Chalutz

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". – Reformer

Answer 5

look like this demo:

DECLARE @Table TABLE (IdRow int not null identity(1,1), MyValue int);
insert into @Table select 345;
insert into @Table select 795;
insert into @Table select 565;

DECLARE @i int = 1;
DECLARE @rows int = (SELECT MAX(IdRow) FROM @Table);

WHILE @i <= @rows
BEGIN
    DECLARE @MyValue int = (Select top 1 MyValue FROM @Table WHERE IdRow = @i);

    print Concat('@i:', @i, ' ', '@MyValue:', @MyValue);

    set @i = @i + 1;
END

Version without idRow, using ROW_NUMBER

DECLARE @Table TABLE (IdRow int not null identity(1,1), MyValue int);
insert into @Table select 345;
insert into @Table select 795;
insert into @Table select 565;

DECLARE @i int = 1;
DECLARE @max int = (select count(*) from @Table);

WHILE @i <= @max
BEGIN
    DECLARE @MyValue int = (

        select T1.MyValue 
        from (
            select MyValue, 
            ROW_NUMBER() OVER(ORDER BY (select 1)) as RowId 
            from @Table
        ) T1 
        where T1.RowId = @i
    );

    print Concat('@i:', @i, ' ', '@MyValue:', @MyValue);

    set @i = @i+1;
END

Barbarity

Answer 6

Here's another answer, similar to Justin's, but doesn't need an identity or aggregate, just a primary (unique) key.

declare @table1 table(dataKey int, dataCol1 varchar(20), dataCol2 datetime)
declare @dataKey int
while exists select 'x' from @table1
begin
    select top 1 @dataKey = dataKey 
    from @table1 
    order by /*whatever you want:*/ dataCol2 desc

    -- do processing

    delete from @table1 where dataKey = @dataKey
end

Longshore

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

Answer 7

Here's my variant. Pretty much just like all the others, but I only use one variable to manage the looping.

DECLARE
  @LoopId  int
 ,@MyData  varchar(100)

DECLARE @CheckThese TABLE
 (
   LoopId  int  not null  identity(1,1)
  ,MyData  varchar(100)  not null
 )


INSERT @CheckThese (MyData)
 select MyData from MyTable
 order by DoesItMatter

SET @LoopId = @@rowcount

WHILE @LoopId > 0
 BEGIN
    SELECT @MyData = MyData
     from @CheckThese
     where LoopId = @LoopId

    --  Do whatever

    SET @LoopId = @LoopId - 1
 END

Raj More's point is relevant--only perform loops if you have to.

Jughead

Answer 8

I didn't know about the WHILE structure.

The WHILE structure with a table variable, however, looks similar to using a CURSOR, in that you still have to SELECT the row into a variable based on the row IDENTITY, which is effectively a FETCH.

Is there any difference between using WHERE and something like the following?

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

DECLARE cursor1 CURSOR  
    FOR @table1
OPEN cursor1  
FETCH NEXT FROM cursor1

I don't know if that's even possible. I suppose you might have to do this:

DECLARE cursor1 CURSOR  
    FOR SELECT col1 FROM @table1
OPEN cursor1  
FETCH NEXT FROM cursor1

Thanks for you help!

Chalutz

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. – Dying

Answer 9

Here is my version of the same solution...

    declare @id int

        SELECT @id = min(fPat.PatientID)
        FROM tbPatients fPat
        WHERE (fPat.InsNotes is not null AND DataLength(fPat.InsNotes)>0)

while @id is not null
begin
    SELECT fPat.PatientID, fPat.InsNotes
    FROM tbPatients fPat
    WHERE (fPat.InsNotes is not null AND DataLength(fPat.InsNotes)>0) AND fPat.PatientID=@id

    SELECT @id = min(fPat.PatientID)
    FROM tbPatients fPat
    WHERE (fPat.InsNotes is not null AND DataLength(fPat.InsNotes)>0)AND fPat.PatientID>@id

end

District

Answer 10

Following Stored Procedure loop through the Table Variable and Prints it in Ascending ORDER. This example is using WHILE LOOP.

CREATE PROCEDURE PrintSequenceSeries 
    -- Add the parameters for the stored procedure here
    @ComaSeperatedSequenceSeries nVarchar(MAX)  
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @SERIES_COUNT AS INTEGER
    SELECT @SERIES_COUNT = COUNT(*) FROM PARSE_COMMA_DELIMITED_INTEGER(@ComaSeperatedSequenceSeries, ',')  --- ORDER BY ITEM DESC

    DECLARE @CURR_COUNT AS INTEGER
    SET @CURR_COUNT = 1

    DECLARE @SQL AS NVARCHAR(MAX)

    WHILE @CURR_COUNT <= @SERIES_COUNT
    BEGIN
        SET @SQL = 'SELECT TOP 1 T.* FROM ' + 
            '(SELECT TOP ' + CONVERT(VARCHAR(20), @CURR_COUNT) + ' * FROM PARSE_COMMA_DELIMITED_INTEGER( ''' + @ComaSeperatedSequenceSeries + ''' , '','') ORDER BY ITEM ASC) AS T ' +
            'ORDER BY T.ITEM DESC '
        PRINT @SQL 
        EXEC SP_EXECUTESQL @SQL 
        SET @CURR_COUNT = @CURR_COUNT + 1
    END;

Following Statement Executes the Stored Procedure:

EXEC  PrintSequenceSeries '11,2,33,14,5,60,17,98,9,10'

The result displayed in SQL Query window is shown below:

The function PARSE_COMMA_DELIMITED_INTEGER() that returns TABLE variable is as shown below :

CREATE FUNCTION [dbo].[parse_comma_delimited_integer]
        (
            @LIST       VARCHAR(8000), 
            @DELIMITER  VARCHAR(10) = ',
            '
        )

        -- TABLE VARIABLE THAT WILL CONTAIN VALUES
        RETURNS @TABLEVALUES TABLE 
        (
            ITEM INT
        )
        AS
        BEGIN 
            DECLARE @ITEM VARCHAR(255)

            /* LOOP OVER THE COMMADELIMITED LIST */
            WHILE (DATALENGTH(@LIST) > 0)
                BEGIN 
                    IF CHARINDEX(@DELIMITER,@LIST) > 0
                        BEGIN
                            SELECT @ITEM = SUBSTRING(@LIST,1,(CHARINDEX(@DELIMITER, @LIST)-1))
                            SELECT @LIST =  SUBSTRING(@LIST,(CHARINDEX(@DELIMITER, @LIST) +
                            DATALENGTH(@DELIMITER)),DATALENGTH(@LIST))
                        END
                    ELSE
                        BEGIN
                            SELECT @ITEM = @LIST
                            SELECT @LIST = NULL
                        END

                    -- INSERT EACH ITEM INTO TEMP TABLE
                    INSERT @TABLEVALUES 
                    (
                        ITEM
                    )
                    SELECT ITEM = CONVERT(INT, @ITEM) 
                END
        RETURN
        END

Karyokinesis

Answer 11

Select Top 1 can easily resolve it without the need of any sequence/order.

Create Function Test_Range()
Returns
@Result Table (ID Int)
As
Begin

Declare @ID Varchar(10) = ''
Declare @Rows Int, @Row Int = 0
Declare @Num Int, @RangeTo Int

Declare @RangeTable Table (ID Varchar(10), RangeFrom Int, RangeTo Int)
Insert Into @RangeTable Values ('A', 1, 10)
Insert Into @RangeTable Values ('B', 25,30)

Set @Rows = (Select Count(*) From @RangeTable)

While @Row <= @Rows
Begin
    Set @Row = @Row + 1
    Select Top 1 @ID = ID, @Num = RangeFrom, @RangeTo = RangeTo  From @RangeTable
    Where ID > @ID
    While @Num <= @RangeTo
    Begin
        Insert Into @Result Values (@Num)
        Set @Num = @Num + 1
    End
End
Return
End

Ortrud