遍历SQL Server中的记录集

Question

我被困在如何循环行集并保存在变量中。

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

 @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表中的每个用户都必须为正数或负数。您能帮忙解决遗失的物品吗？

Answer 1

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

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