At some point you may want to loop through every row in a table and perform operations based on the fields in each row. After a bit of investigation, I've found the 2 most common ways of doing it.
Loop Through All Rows of a Table Using a Counter
This method requires that the primary key is an integer. It's also not the most efficient since it will loop as many times as the Max ID, even if there are thousands of missings rows. For example, your table may have 3 rows with ID's 1, 2, and 1000. The loop will execute 1000 times for only 3 rows.
use
YourDatabaseNameHere
go
declare @MaxID int
declare @Counter int
set @Counter = 1
select @MaxID = max(YourPrimaryKeyColumnNameHere) from YourTableNameHere
while @Counter <= @MaxID
begin
-- Perform Operations
declare @Field1 varchar(10)
declare @Field2 int
set @Field1 = Field1, @Field2 = Field1 from YourTableNameHere where YourPrimaryKeyColumnNameHere = @Counter
-- update counter
set @Counter = @Counter + 1;
end
go
Loop Through All Rows of a Table Using a Cursor
This method will loop through all rows of a table regardless of what type the primary key is.
use
YourDatabaseNameHere
go
declare @Field1 int
declare @Field2 int
declare @Field3 int
declare MyCursor cursor fast_forward for
select Field1, Field2, Field3 from YourTableNameHere
open MyCursor
fetch next from MyCursor
into @Field1, @Field2, @Field3
while @@fetch_status = 0
begin
-- Perform Operations
declare myTest int
myTest = @Field1 + @Field2 + @Field3
-- Advance the Cursor
fetch next from MyCursor
into @Field1, @Field2, @Field3
end
close MyCursor
deallocate MyCursor
go