Looping over a recordset in SQL Server

Question

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

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

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?

Answer 1

You should be able to do this in a couple of statements without the use of a cursor or other procedural code. Just make sure that it's all in one transaction:

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

Of course, add proper error handling and be sure to test this first.

Also, I slightly altered some of your table and column names. I don't want to get into which specific naming conventions are better than others, but at least be consistent. If you're going to use underscores, use them. If you're going to use camel-back notation instead then use that, but don't mix them. The same goes for plural vs. singular table names.

Answer 2

For SQL 2005 and up:

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

Since there's been some confusion I will note that -Deleted.Acct_Balance is all you need to bring the balance to zero. It negates negative balances to insert a positive, and negates positive balances to insert a negative.

For SQL 2000 you'll need multiple statements.

Answer 3

As a general rule, thinking you need to do row-based operations in SQL is a pretty good indication that you (or someone else) has framed the problem incorrectly: it is a code smell indicative of procedural rather set-based thinking.

The pseudocode you want is, I think, something along these lines:

-- 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

Answer 4

You are looking for a function that returns @balance if @balance>0, else -@balance.

ABS(@balance) would do that. (Unless you need to insert a string literal starting with a "-" but that seems odd - I'd assume the Balance column is a decimal type.)

I'm guessing at the business domain, but there probably is also a credit/debit column that you would need to set according to the sign of the transaction. In which case you may want something like:

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 ); 

Answer 5

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

Answer 6

I would take a look at cursors, however IMO this is better done in the application logic than in SQL.