Welcome to ciysys blog

Loop all records

Published on: 18th Oct 2019

Updated on: 19th May 2021

Overview

Cursor requires record locking the live data and it might not work as fast as you think. Especially if you have a busy database. The alternative way is to use a temporary working table which is easier to work with.

In this article, we are going to use a local temporary working table to keep the data and then loop through all records.

Here's how we achieve looping without using cursor

The following example prints all table names in the current database.

set nocount on

declare
    @seq int
    , @name nvarchar(255)

-- declare a temporary table in the memory.
create table #tb (
    seq int identity(1,1)
    , name nvarchar(255)
)

--copy all the necessary records to the temporary table '#tb'
insert into #tb (name)
select name
from sys.objects
where
    type = 'u'
order by name

-- init the var before use
set @seq = 0

-- start the loop
while exists(
    select *
    from #tb
    where seq > @seq
)
begin
    -- always load the first record
    select top 1                --<<=== this is very important!!!
        @seq = seq
        , @name = name
    from #tb
    where seq > @seq
    order by seq                --<<=== this is very important!!!

    -- do whatever necessary process here.
    -- you may use 'set xxx' to join the sql string before printing it out.
    print '@name => ' + @name
end

drop table #tb

Basically, this is how it works

  1. It declared a local temporary table call #tb. The most important field is seq which is an auto-increment field. That means, inserting a new record into this table, SQL server will assign a new unique running number for that record.

  2. We must initialize @seq with the value of 0. Otherwise, the WHILE loop will not start.

  3. The WHILE loop starts and checks if there is any record that is greater than @seq value. If yes, it will retrieve the first record that is greater than @seq.

  4. Then, it does some data operations.

  5. Upon completely looping through all records, it drops the local temporary table.

Table variable VS Local temporary table

From our experience, for the number of records that is less than 100, we prefer using table variables. Otherwise, we use a local temporary table. The reason is due to the performance.

SQL development VS C# development

SQL server is able to process the data fast and it will still be slow when you try to loop the record. As a developer, we should not always use the C# development concept to solve the data issue. Avoid looping unless there is no other choice.

When not to use loop

When is the appropriate time to use loop

Jump to #MSSQL blog

Author

Lau Hon Wan, software developer.