Microsoft SQL Server 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