| Kenneth Downs 2005-04-28, 3:24 am |
| Simon Hayes wrote:
> If you want to see the calculated total immediately, then a view is
> probably better than a procedure:
>
> create view dbo.OrdersWithTotalCost
> as
> select
> OrderID,
> OrderItemID,
> ...
> /* Other columns from Orders */
> ...
> Quantity,
> Cost,
> Quantity * Cost as 'Total'
> from
> dbo.Orders
>
> I'm not sure I understand your concern about hitting the database again
> - since your calculation is so simple, you will know the value of Total
> before you even INSERT the new order item, and you may not need to
> retrieve it again (unless there's further processing in the database,
> of course).
>
> If you really want to avoid another query, then one option is to create
> an InsertOrderItem stored procedure, which INSERTs the new item and
> then returns the total as an output parameter.
>
My original suggestion held that the definitions should be stored in a data
dictionary and any implemention, views or sprocs, should be generated from
that.
If you do that, the client (some OO code) can read the dictionary, or you
can generate code for classes, and they can do their own calculations on
the fly for user convenience. You then can independently decide how to
implement the same formulas on the server.
If the implementation is based on a dd, you can try different methods and
change your mind rather painlessly.
--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(
Dat)a(.com)
|