Loop through table by row T-SQL

Question

In T-SQL I want to loop through a table in a stored procedure, by reading a row by row.

DECLARE @IMAX INT,
        @ICOUNT INT,
        @INTERFACE_ID_36 INT,
        @INTERFACE_ID_38 INT

SELECT * FROM INTERFACE_36_DATA

SET @IMAX = @@ROWCOUNT
SET @ICOUNT = 1

WHILE(@ICOUNT <= @IMAX)
    BEGIN
        SELECT @INTERFACE_ID_36 = Interface_ID
        FROM INTERFACE_36_DATA
        WHERE ROW_NUMBER() OVER (ORDER BY id) AS  = @ICOUNT   --syntax error here

            IF @INTERFACE_ID_36 = 10
                SET @INTERFACE_ID_38 = 0
            ELSE IF @INTERFACE_ID_36 =

so what's the problem/issue/query? - you can use While loop as well as cursor too. — Pedram, Jan 11 '16 at 05:03
In T-SQL, the general rule is: try to **avoid** the RBAR (row-by-agonizing-row) processing as much as possible. Use the "native" SQL operations which are **set-based** - much easier, much faster — marc_s, Jan 11 '16 at 05:09

score 5 · Accepted Answer · answered Jan 11 '16 at 06:14

I suggest to rewrite it using Cursors as follows faster:

DECLARE @IMAX INT,
        @ICOUNT INT,
        @INTERFACE_ID_36 INT,
        @INTERFACE_ID_38 INT

DECLARE db_cursor CURSOR FOR  
SELECT Interface_ID FROM INTERFACE_36_DATA  

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @INTERFACE_ID_36   

WHILE @@FETCH_STATUS = 0   
BEGIN
       SELECT @INTERFACE_ID_36
       -- All your other selects  

       FETCH NEXT FROM db_cursor INTO @INTERFACE_ID_36   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

score 0 · Answer 2 · edited May 23 '17 at 12:03

0

Change your query like below. As we can't use row_number in where condition. Check the link here for more descr

WITH    cte
          AS ( SELECT   Interface_ID ,
                        ROW_NUMBER() OVER ( ORDER BY id ) AS RN
               FROM     INTERFACE_36_DATA
             )
    SELECT  @INTERFACE_ID_36 = Interface_ID
    FROM    cte
    WHERE   RN = @ICOUNT

edited May 23 '17 at 12:03

Community

1
1

answered Jan 11 '16 at 05:45

Shiju Shaji

1,682
17
24

score 0 · Answer 3 · answered Jan 11 '16 at 06:29

You can try doing this :

DECLARE @IMAX INT,
        @ICOUNT INT,
        @INTERFACE_ID_36 INT,
        @INTERFACE_ID_38 INT
select *,ROW_NUMBER() OVER (ORDER BY Interface_ID) RowId  into #tmptbl from INTERFACE_36_DATA

Declare @I BIGINT = 1    
declare @count BIGINT = (select Count(*)  from #tmptbl)    
while(@I < = @count)    
  begin  

        SELECT @INTERFACE_ID_36 = Interface_ID
        FROM #tmptbl
        where   RowId = @I

        --- do yor additional stuff
   set @I =@I +1    
  end

Loop through table by row T-SQL

3 Answers3