Блог

Я застрял на том, как перебирать набор строк и сохранять в переменной.

Имейте в виду, это может быть псевдокод, потому что 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 быть положительным или отрицательным числом. Не могли бы вы помочь с недостающими частями?

Вы должны быть в состоянии сделать это с помощью пары инструкций без использования курсора или другого процедурного кода. Просто убедитесь, что все это в одной транзакции:

 BEGIN TRANSACTION

INSERT INTO crrsql.dbo.AR_Transactions (
    cashier_id,
    cust_num,
    balance,
    transaction_date)
SELECT
    100199,
    cust_num,
    -acct_balance,
    DATEADD(MINUTE, -30, current_date)
FROM crrsql.dbo.Customers
WHERE acct_balance <> 0

UPDATE crrsql.dbo.Customers SET acct_balance = 0 WHERE acct_balance <> 0

COMMIT TRANSACTION
  

Конечно, добавьте надлежащую обработку ошибок и обязательно сначала протестируйте это.

Кроме того, я немного изменил некоторые из ваших имен таблиц и столбцов. Я не хочу вдаваться в то, какие конкретные соглашения об именовании лучше других, но, по крайней мере, будьте последовательны. Если вы собираетесь использовать символы подчеркивания, используйте их. Если вы собираетесь использовать вместо этого верблюжью нотацию, используйте это, но не смешивайте их. То же самое касается имен таблиц во множественном и единственном числе.

Для SQL 2005 и выше:

 UPDATE C
SET C.Acct_Balance = 0
OUTPUT 100199, Inserted.CustNum, -Deleted.Acct_Balance, DateAdd(Minute, -30, GetDate())
INTO crrsql.dbo.AR_Transactions (Cashier_ID, CustNum, Balance, Transaction_Date)
FROM crrsql.dbo.Customer C
WHERE C.Acct_Balance <> 0
  

Поскольку возникла некоторая путаница, я отмечу, что -Deleted.Acct_Balance это все, что вам нужно, чтобы свести баланс к нулю. Он сводит на нет отрицательные остатки для вставки положительного значения и отрицает положительные остатки для вставки отрицательного.

Для SQL 2000 вам понадобится несколько инструкций.

Как правило, мысль, что вам нужно выполнять операции на основе строк в SQL, является довольно хорошим признаком того, что вы (или кто-то другой) неправильно сформулировали проблему: это запах кода, указывающий на процедурное мышление, скорее основанное на множествах.

Я думаю, что псевдокод, который вы хотите, это что-то вроде этих строк:

 -- Create a temporary table. A table starting with # is a temporary. It will be
-- automatically dropped when the session ends. If two sessions creates temp
-- tables with the same name, at the same time, they will still get one table each.
create table #work
(
  CustNum int ,
  Balance money ,
)

insert #work ( CustNum , Balance )
select CustNum , Balance
from Customer
where Balance != 0

begin transaction

insert dbo.AR_Transactions (cashier_ID, CustNum, Balance)
select cashier_ID = 100199 ,
       user       = CustNum ,
       adjustment = case sign(@balance) -- should always be  1 or -1
                    when  1 then @balance -- positive balance
                    when -1 then -@balace -- negative balance
                    end 

update Customer set balance = 0
from Customer c
join #work    w on w.CustNum = c.CustNum

commit transaction

-- Manual tidying up if the connection might be kept open.
drop table #work
  

Вот прямой перевод вашего исходного кода в допустимый синтаксис 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
  

Вы ищете функцию, которая возвращает @balance, если @balance>0, в противном случае -@balance.

ABS (@balance) сделал бы это. (Если только вам не нужно вставить строковый литерал, начинающийся с «-«, но это кажется странным — я бы предположил, что столбец баланса имеет десятичный тип.)

Я предполагаю, что это бизнес-домен, но, вероятно, там также есть столбец credit / debit, который вам нужно будет установить в соответствии со знаком транзакции. В этом случае вам может понадобиться что-то вроде:

 INSERT INTO [crrsql].[dbo].[AR_Transactions] (cashier_ID, CustNum, Balance, CR_DR ) VALUES (100199, user, ABS(@balance), CASE WHEN @balance > 0 THEN 'CR' ELSE 'DR' END ); 
  

Я бы взглянул на курсоры, однако, IMO, это лучше сделать в логике приложения, чем в SQL.