help on a sql request

Hi,
I have quite a complicated request to do in sql.
I've got on table with 3 fields (id, field1, field2) and I have to
process a request on each of the records, one by one, and then
determine if the status is OK for each records
For example, I would check if the sum of field1 and field2 is greater
than 10.
What is the best way to do this ? It has to be done in a stored
procedure. I can't return the status for each one of the records, so
should I store it in a temporary table and do a select on it from my
tier application (vb.net) ?
Thx
SamWhat does "process a request" mean? If it's just a data manipulation
process then probably it can be done with a SELECT statement or other
SQL DML statements. You seem to be describing a cursor-type of process
but on the information you've given there is no evidence that that
would be necessary or desirable.

--
David Portas
SQL Server MVP
--|||Yes that's pretty much it. I would like to have a cursor-like
behaviour. Maybe not using cursors as I'm aware of their poor
performances.
By "process a request" I meant that on each row, I proceed to
operations (what I do there is not important). So I want a way to be
able to process each row one by one and to actually store the result
somewhere.|||> By "process a request" I meant that on each row, I proceed to
> operations (what I do there is not important).

What you do there IS important - it's the whole issue! If your process
just modifies some data in the database for example then you probably
won't need to do it row-by-row because usually it will be possible to
write set-based code to do the whole thing. That might mean replacing a
stored procedure with a new piece of code that doesn't have to be
called for each row but it's probably possible and it may well be worth
doing.

If you are doing something inherently procedural (probably something
outside the database such as sending an email or exporting a file) then
yes, you will need a cursor. Other alternative loops are just cursors
by another name and don't offer much advantage over the regular DECLARE
CURSOR... WHILE constructs. The problem isn't "cursors" per-se, it is
row-by-row processing that you should aim to avoid.

--
David Portas
SQL Server MVP
--|||"Sam" <samuel.berthelot@.voila.fr> wrote in message
news:1111590721.314957.22430@.g14g2000cwa.googlegro ups.com...
> Hi,
> I have quite a complicated request to do in sql.
> I've got on table with 3 fields (id, field1, field2) and I have to
> process a request on each of the records, one by one, and then
> determine if the status is OK for each records
> For example, I would check if the sum of field1 and field2 is greater
> than 10.
> What is the best way to do this ? It has to be done in a stored
> procedure. I can't return the status for each one of the records, so
> should I store it in a temporary table and do a select on it from my
> tier application (vb.net) ?
> Thx
> Sam

CREATE PROCEDURE junk
AS
SELECT id, Case When field1+field2 > 10 then 'Y' else 'N' END as Status
GO

Regards,
Jim|||thanks, but it was just for the example ;) what I do is much more
complicated than field1 + field2.

David> I know I should avoid doing row by row processing but I think I
have no other way around. I will use a loop on the primary key.
thx|||"Sam" <samuel.berthelot@.voila.fr> wrote in message
news:1111597760.009196.271810@.f14g2000cwb.googlegr oups.com...
> thanks, but it was just for the example ;) what I do is much more
> complicated than field1 + field2.
> David> I know I should avoid doing row by row processing but I think I
> have no other way around. I will use a loop on the primary key.
> thx

Sorry, I was being a bit facetious. Your question indicted that you needed
to return a result set that included a status based on a calculation. I
presented a method for doing that using CASE. You are presuming that you
need row by row processing but haven't really indicated why that might be
so. How do you determine the status for each row?

Regards,
Jim|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

>> I've got on table with 3 fields [sic] (id, field1, field2) and I
have to process a request on each of the records [sic], one by one, and
then determine if the status is OK for each records [sic] <<

Rows are not records; columns asre not fields; these are totally
different concepts. You are still carrying a mental model of a
sequential file system and not an RDBMS. You even say "process" instead
of "query".

After many years with SQL and six books on the language, I have written
five cursors in actual applications. Three of them could hav been
avoided if we had a CASE expression back them. The other two should
have been done in another language and were basically version of the
traveling salesman problem where I just needed the first answer under a
certain value, rather thant he st of all answers.

>> What is the best way to do this ? <<

Since we have no idea what the desired results are, over even what the
data looks like, it is hard to say. I tend to use VIEWs when I have a
computation to display to the users. Stored procedures are required if
I have to make changes to the base tables. In this case, it sounds like
you would have a computed column named "foobar_status" or whatever and
the rule for computing it.

>> It has to be done in a stored procedure. <<

You have already decided on the method! Don't do that; start with
specs and a goal, then pick a method.

>> I can't return the status for each one of the records [sic], so
should I store it in a temporary table and do a select on it from my
tier application (vb.net) ? <<

In the 1950's, ths would have been a scratch tape instead of a temp
table. Same design, mimicked in SQL.

Also, doesn't VB, along with most other host languages, have a way to
get a full result set to the front end?