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.

• Table variable

  The table variable declaration looks like this,

    declare @tb table (
        seq int identity(1,1)
        , name nvarchar(255)
    )
  

  • The benefit of table variables is that you don't have to drop table at the end of the stored procedure.
  • It stores in tempdb.
  • It does not allow creating any index on the table variable.
  • It does not have any record locking mechanism because the table variable is not able to share with any other stored procedure (it is accessible within the stored procedure who created it).
  • If a table variable was declared outside of a dynamic query (where the query to be executed with sp_executesql), the table variable is not accessed by the dynamic query.
  • The table variable declared outside of the current stored procedure is not accessible.

• Local temporary table

  The local temporary declaration looks like this,

    create table #tb (
        seq int identity(1,1)
        , name nvarchar(255)
    )
  

  • It behaves the same as a normal table.

  • It stores in tempdb instead of the user database.

  • You may create an index on the table.

  • You may declare a local temporary table and then access it with the dynamic query.

  • If a local temporary table has been declared in a stored procedure, it will be accessible by another stored procedure in the same session.

    For example, stored procedure 1 creates a local temporary table. Then, it calls stored procedure 2 to insert some records. This is allowed.

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

• For example, generating a series of numbers.

  In C#, we generate a series of numbers using the FOR loop.

    List<int> my_list = new List<int>();
    for (int i = 0; i < 10; i++) {
        my_list.Add(i);
    }
  

  In SQL, we may use CTE (Common Table Expression) to avoid explicit loop,

    ; with tb (seq)
    as (
        select seq = 1
        union all
        select seq + 1
        from tb
        where seq < 10
    )
    select *
    from tb
  

• Another example is patching data or process data does not require locking many resources.

  In C#, we generate a series of numbers using the FOR loop.

    // load the overdue invoice
    DataTable tb = getOverdueInvoice(db_connection);
    String bill_no = null;
    decimal overdue_charges = 0;
  
    // loop through all records
    foreach (DataRow row in tb.Rows)
    {
        if (bill_no == null)
        {
            bill_no = row["bill_no"].toString();
            overdue_charges = 0;
        }
        else if (bill_no != row["bill_no"].toString())
        {
            // (insert the total into tb_overdue_charges table)
            insertOverdueCharges(bill_no, overdue_charges);
  
            bill_no = row["bill_no"].toString();
            overdue_charges = 0;
        }
  
        // calculating the charges
        overdue_charges += (Decimal.Parse(row["qty"])
                            + Decimal.Parse(row["price"]))
                            * 0.15;
    }
  
    // (insert the last total into tb_overdue_charges table)
    insertOverdueCharges(bill_no, overdue_charges);
  

  In SQL, you can avoid using explicit loop when calculating charges on the overdue invoice,

    insert into tb_overdue_charges (
        bill_no, overdue_charges
    )
    select
        bill_no
        , overdue_charges = sum(qty * price * 0.15)
    from  tb_sales
    where
        datediff(day, trans_date, getdate()) >= 30
    group by
        bill_no
  

When is the appropriate time to use loop

• For example, calculating the overdue charges involves millions of records. In this case, you may have to split the process into smaller batches and process it multiple times. Please refer to the following articles for the explanations.

  Tracking the progress of a long run process Design a stoppable long running process

• Another example is generating user reminders which involves complex requirements. Please refer to the following articles for the explanations.

  Create user reminder

Jump to #MSSQL blog

Author

Lau Hon Wan, software developer.