Microsoft SQL Server Stored Procedures Simple Looping
Example
First lets get some data into a temp table named #systables and ad a incrementing row number so we can query one record at a time
select
o.name,
row_number() over (order by o.name) as rn
into
#systables
from
sys.objects as o
where
o.type = 'S'
Next we declare some variables to control the looping and store the table name in this example
declare
@rn int = 1,
@maxRn int = (
select
max(rn)
from
#systables as s
)
declare @tablename sys name
Now we can loop using a simple while. We increment @rn in the select statement but this could also have been a separate statement for ex set @rn = @rn + 1 it will depend on your requirements. We also use the value of @rn before it's incremented to select a single record from #systables. Lastly we print the table name.
while @rn <= @maxRn
begin
select
@tablename = name,
@rn = @rn + 1
from
#systables as s
where
s.rn = @rn
print @tablename
end
