SQL Server Loop - How to Loop Through a Set of Records

Loop through table by row T-SQL

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

Is there a way to loop through a table variable in TSQL without using a cursor?

First of all you should be absolutely sure you need to iterate through each row — set based operations will perform faster in every case I can think of and will normally use simpler code.

Depending on your data it may be possible to loop using just SELECT statements as shown below:

Declare @Id int

While (Select Count(*) From ATable Where Processed = 0) > 0
Begin
    Select Top 1 @Id = Id From ATable Where Processed = 0

    --Do some processing here

    Update ATable Set Processed = 1 Where Id = @Id 

End

Another alternative is to use a temporary table:

Select *
Into   #Temp
From   ATable

Declare @Id int

While (Select Count(*) From #Temp) > 0
Begin

    Select Top 1 @Id = Id From #Temp

    --Do some processing here

    Delete #Temp Where Id = @Id

End

The option you should choose really depends on the structure and volume of your data.

Note: If you are using SQL Server you would be better served using:

WHILE EXISTS(SELECT * FROM #Temp)

Using COUNT will have to touch every single row in the table, the EXISTS only needs to touch the first one (see Josef's answer below).

Loop through table while records keep getting added to the table

I'd use a do while loop instead.

Have a variable called moreToProcess data type boolean.

Set the value before entering the loop using execute SQL statement based on count > 0

First step in the loop is to get a record to process using exec SQL.

Before exiting loop rerun the SQL to reset the variable.

Set to loop while moreToProcess is true.

Loop through each row in a table for a given range

Jim showed how you can get the desired result using a loop but pointed out that loops are not the way to go. In SQL Server, the correct way to do this would be to use a numbers table or function, such as fnTally.

declare @x date ='01-jan-2021'
declare @y date ='31-jan-2021'

SELECT SomeDate = DATEADD(DAY,f.N,@x) FROM dbo.fnTally(0,DATEDIFF(DAY,@x,@y)) AS f;

Returns:

SomeDate
----------
2021-01-01
2021-01-02
2021-01-03
...
2021-01-29
2021-01-30
2021-01-31

Much faster and efficient with much less code.

---

Related Topics