Iterating through rows in SQL Server

Iterate through each row in table using while loop in SQL

Hi,

You may face a situation in which you need to iterate through rows in a table one at a time and perform some operation on the data of the row. Note that, set based operations are highly efficient in SQL server and row based operation is not recommended. Suppose, you have a table and you need to run an SP on each row of the table. One possible solution is to use cursors, which are complex to write and also not very efficient. An alternative and easier method is to use the while loop.

You will need to copy rows from the original table into a temp table. Let’s create a temp table to use in this example:

create table #tempList(Seq int, Color varchar(10))

insert into #tempList values(1, 'Red')
insert into #tempList values(2, 'Green')
insert into #tempList values(3, 'Blue')
insert into #tempList values(4, 'Black')
insert into #tempList values(5, 'White')

Here, we are manually inserting rows, you can use select into to insert rows into temp table from the original table. Now, let’s use while loop to go through each row of this temp table and print it. You can store column values in some variables and do your operations like calling SPs.

declare @Seq int
declare @Color varchar(10)

while (select count(*) From #tempList) > 0
begin

-- get the row
select top 1 @Seq = Seq, @Color = Color from #tempList

-- perform some operation
select @seq, @Color

-- delete it from temp table
delete from #tempList where Seq = @Seq

end

We are selecting top 1 row and then performing some operation on it (printing in our case). Then, we delete this row from the temp table and repeat the aforementioned steps until there are no rows left in the temp table.

This method may not be the best one in terms of execution time and dependency on temp table, but works well for one-time tasks such as quick report generation directly through SQL Server Management Studio.

Cheers!
JS