Looping Through Each Table Row in SQL Server

Jes Sie

I have 2 tables (Header and Details). The primary key for the header is PolicyNo and is a foreign key to the Details table. Each table has a field name IsPaid (bit). Every time the customer will pay to the cashier, the IsPaid field of the Details table will be updated to true. If all rows of the Details table will all be true, then the Header table IsPaid field should also be updated to true. This is easy if all rows will be paid at once. But often, it is not paid at the same time. Below is a scenario to make things clear.

A policy was created with 10 persons insured. Only 7 persons insured were paid. So, the Header IsPaid field is not set to true. After several days, the remaining 3 persons paid their account. So now, all the IsPaid field of the details table was set to true.

Question: How can I loop through each row in the Details table and check whether the IsPaid field is already true? if all rows were true, how can I update the IsPaid field of the Header table?

Thank you all for your help

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke4y ago
Accepted answer
You don't really have to loop through each in SQL Server. In fact, you shouldn't be doing it unless you have to.

Shown below is the solution to your problem, if I have understood it correctly.

WARNING: Please test it some environment other than production or take copy of table or take backup of db. I will leave that to you.
declare @policyInfo table
(
   [PolicyNo]   integer not null primary key clustered
  ,[RowsTotal]  integer not null
  ,[RowsPaid]   integer not null
)

insert into @policyInfo
select [PolicyNo], count(1), sum(convert(integer, [IsPaid]))
from [dbo].[PolicyDetail]
group by [PolicyNo]
order by [PolicyNo]

-- You can comment the following line after you are happy with the result.
select * from @policyInfo


update [dbo].[PolicyHeader] set [IsPaid] = 1
where [PolicyNo] in
  (
    select [PolicyNo]
    from @policyInfo
    where [RowsTotal] = [RowsPaid]
  )
Jes Sie4y ago
Thanks for the response Yazhini.
Jes Sie4y ago
Thank you, Nitin. This is actually what I was looking for. In my mind, the codes could be very long. But, you made it very simple and easy. I learned a new knowledge today. Thanks again.
Sachin Singh4y ago
You don't need to loop through one by one, instead, you can check at once using Exists method of SQL server and update the header table accordingly
IF EXISTS (SELECT * FROM Details WHERE [IsPaid] <>1)
begin
  Print 'not paid for all'
end
else
begin
UPDATE Header set IsPaid=1
end
Yazhini MP4y ago
Here, I have an idea, a sample solution

declare @idColumn int
select @idColumn = min(TableID) from Table
while @idColumn is not nyll
begin
/*
Do all the stuff that you need to do
*/
select @idcolumn = min(TableID) from Table where TableID>@idColumn
end
Jes Sie4y ago
Hello, Nitin thank you for your reply. It seems that doing the loop in the SQL server is too difficult for me. So, I decided to do the loop in C# after the cashier will process the payment. Below is my code and the answer to my problem. If you or anyone can improve my code, please do so.
int checkedRows = 0;
                    for (int i = 0; i <= gvHPAInsuredList.Rows.Count - 1; i++)
                    {
                        bool policySelected = ((CheckBox)gvHPAInsuredList.Rows[i].FindControl("chkDetails")).Checked;
                        Label lblinsuredPolicyID = (Label)gvHPAInsuredList.Rows[i].FindControl("lblinsuredPolicyID");
                        string insuredPolicyID = lblinsuredPolicyID.Text;
                        if (policySelected == true)
                        {
                            //update HPA PolicyDetails table set ispaid = 1
                            HPAPolicyDataAccess.UpdateHPAPolicyDetailsIsPaid(insuredPolicyID);

                            //count every checked rows
                            checkedRows = checkedRows + 1;
                        }

                    }

                    int rowCount = gvHPAInsuredList.Rows.Count;
                    int isAllChecked = rowCount - checkedRows;
                    if(isAllChecked == 0)
                    {
                        //Update Policy header IsPaid field
                    }
Nitin Sontakke4y ago
Where is this code being written?

Stored procedure, C#.

You will need to tell us everything to help you better. Please DO NOT make any assumptions.