|
|
Posted by David Portas on 11/29/06 07:08
Mohd Al Junaibi wrote:
> 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?
It seems very likely that you could retrieve the whole result in one
query without looping through an array several times. This is a key
difference between procedural
languages and SQL. Stop thinking procedurally (loops and arrays) and
start thinking about set-based queries instead! :-)
Unfortunately, you haven't given enough information to get a full
answer. What we need to know are: the base table structures (post some
simplified CREATE TABLE statements but make sure you include the keys);
some sample data (post INSERT statements); your expected end result.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|