|
|
Posted by Mohd Al Junaibi on 11/29/06 06:26
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.
>>>END of Logic<<
Problem:
This kind of dataset or report usually takes alot of time, and I need
to have the ability to storing all the calculated variables for each
"Office" in an "array" so that I can do ONE INSERT STATEMENT LOOP as
opposed to doing one insert statement at a time, in a loop.
Basically, a loop to calculate and save into an array, and then one
loop for insert statements.
Any suggestions gentlemen?
Navigation:
[Reply to this message]
|