Inserting Multiple Rows Into One Table (with Calculated Fields)

Hello all,

my first post here...hope it goes well. I'm currently working on
stored procedure where I translated some reporting language into T-SQL

The logic:

I have a group of tables containing important values for calculation.
I run various sum calculations on various fields in order to retrieve
cost calculations ...etc.

1) There is a select statement which gathers all the "records" which
need calculations.
ex: select distinct Office from Offices where OfficeDesignation =
'WE' or OfficeDesignation = 'BE...etc.
As a result I get a list of lets say 5 offices which need to be
calculated!

2) A calculation select statement is then run on a loop for each of
the returned 5 offices (@OfficeName cursor used here!) found above.An
example can be like this

(* note that @WriteOff is a variable storing the result):

"select @WriteOff = sum(linecost * (-1))
From Invtrans , Inventory
Where ( transtype in ('blah', 'blah' , 'blah' ) )
and ( storeloc = @OfficeName )
and ( Invtrans.linecost <= 0 )
and ( Inventory.location = Invtrans.storeloc )
and ( Inventory.itemnum = Invtrans.itemnum )"...etc

This sample statement returns a value and is passed to the variable
@WriteOff (for each of the 5 offices mentioned in step 1). This is done
around 9 times for each loop! (9 calculations)

3) At the end of each loop (or each office), we do an insert statement
to a table in the database.



Quote: