Hi, friends,

I need to write a stored procedure to return records selected from 2 tables.
However, before returning those records, values of some fields need to be
recaculated with up-to-date data in some other tables of the same database,
and the original field values in that 2 tables should keep the same. In
another word, sp returned record values are not the values in db tables, but
recalculated ones, and their original values should not be updated.

So far I have two ways to do it:
(1) In sp, create a view with values in the 2 tables, update values, return
updated records, drop the view;
(2) Create a table in db especially for this purpose, say volatileTable.
Then, in sp, delete all records first, insert new records with values from 2
tables, update values, return updated records.

I am not sure which one is better, and any other better approaches?

Thanks a lot.

Andrew wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Create one query that returns the results of the calculations in the
result set. E.g.:

SELECT A.ColA * B.ColB As Result
FROM tableA As A INNER JOIN tableB As B
ON A.ID = B.ID
WHERE < criteria >
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmVSDoechKqOuFEgEQIbkwCff49oHcM1n6LQJUbBfyTKZv uE7zUAoOYO
0GSmwyaPYPrgZLpipCKOwrnT
=VTo8
-----END PGP SIGNATURE-----

In the SP,
1) Update the columns in the table that need to be updated, and then
2) Select the updated values from the table to be returned to the client.


"Andrew" wrote:

those columns can not be updated. Although updated value need to be returned,
the original values must be kept.

"CBretana" wrote:

the calculation can not be done by a query, or it is very hard, e.g.,
currently we use 5 select statements to determine a value for a field
indicating if a package contains a new item, or if each item's property is
the same, or if this item is replaced with a new version, when comparing with
another package.

"MGFoster" wrote:

=?Utf-8?B?QW5kcmV3?= <Andrew (AT) discussions (DOT) microsoft.com> wrote in
news:912F17CC-E359-457E-B2F3-6F15DE06034D (AT) microsoft (DOT) com:

How about using the query to populate a local temporary table
(##TableName), and then modifiying the values as necessary before selecting
them from the temp table to return to the user?

--
Rumble
"Write something worth reading, or do something worth writing."
-- Benjamin Franklin

#tempTableName is another way. What else?

"Rumbledor" wrote:

Oh, then why can't you just Select the values you want the SP to return?
You know that the items returne in the output of a Select Statement do not
have to be cdirect references t ocolumns in a table, they can be arbitrarily
complex expressions based on the values of multiple columns plus constants or
input parameters...

"Andrew" wrote:

=?Utf-8?B?QW5kcmV3?= <Andrew (AT) discussions (DOT) microsoft.com> wrote in
news:166BD5A4-E97D-4D27-9820-B33E47E4C002 (AT) microsoft (DOT) com:

Actually, #tempTableName would be the correct way to create a *local*
temp table, right you are. Got my #'s mixed up. :P

I will say that I've seen some pretty complex queries rolled up into a
view or stored procedure, so you can quite likely meet your requirements
in either. Perhaps if you included some DDL we could confirm or
eliminate that possibility.



--
Rumble
"Write something worth reading, or do something worth writing."
-- Benjamin Franklin