遍历SQL Server中的记录集 [英] Looping over a recordset in SQL Server

问题描述

我对如何循环行集并保存在变量中一无所知.

I am stuck on how to loop over a rowset and save in a variable.

请注意，这可能是伪代码，因为SQL不是我的专长.

Mind you, this may be pseudo-code because SQL is not my specialty.

 @all_customers = select CustNum from [crrsql].[dbo].[Customer];
 some loop(@all_customers as user)
 //I need to find out what the Acct_balance field is and either subtract or add to bring all the balances to 0
    @balance = select Acct_balance from [crrsql].[dbo].[Customer] where CustNum = user;
    if @balance > 0 
      update [crrsql].[dbo].[Customer] set Acct_balance = 0;
      INSERT INTO [crrsql].[dbo].[AR_Transactions] (cashier_ID, CustNum, Balance) VALUES (100199, user, @balance); 
    else
      update [crrsql].[dbo].[Customer] set Acct_balance = 0;
      INSERT INTO [crrsql].[dbo].[AR_Transactions] (cashier_ID, CustNum, Balance) VALUES (100199, user, "-" + @balance); 
    end
 end loop

如您所见，我正在遍历客户，在该循环中，我需要获取当前余额并将其设置为零，但首先我需要找出它是正数还是负数才能弄清楚如果AR_Transactions表中每个用户的插入数必须为正数或负数.您能帮忙解决遗失的物品吗?

As you can see I am looping through the customers and within that loop I need to get the current balance and set it to zero, but first I need to find out if it's a positive or negative number to be able to figure out if the insert per user in the AR_Transactions table needs to be a positive or negative number. Could you help with the missing pieces?

推荐答案

在这里，您可以将原始代码直接转换为有效的SQL Server语法.我无法谈论围绕您正在做的事情的业务规则，但是请注意，这种方法避免了使用游标，而是使用ABS()函数来消除原始的if/else块.

Here is a direct translation of your original code into valid SQL Server syntax. I can't speak to the business rules surrounding what you are doing, but note that this approach avoids the use of cursors and uses the ABS() function to do away with your original if/else block.

declare @all_customers as table(
  customernumber int
);

/*
--you can insert dummy data for testing purposes like so:
insert into @all_customers
select 5, 1
union
select 2, 1
--*/


while (0 < (select count(*) from @all_customers)) begin
  declare @current_customer int = (select top 1 customernumber from @all_customers);

  declare @balance money = (select acct_balance from [crrsql].[dbo].[Customer] where CustNum = @current_customer);
  update [crrsql].[dbo].[Customer] set Acct_balance = 0;
  INSERT INTO [crrsql].[dbo].[AR_Transactions] (cashier_ID, CustNum, Balance) VALUES (100199, user, abs(@balance)); 

  delete @all_customers where customernumber = @current_customer;
end

这篇关于遍历SQL Server中的记录集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！