|
|
Posted by Robert Klemme on 10/01/28 11:43
Disclaimer: difficult to answer without more specific info.
JayCallas@hotmail.com wrote:
> I know this question has been asked. And the usual answer is don't use
> cursors or any other looping method. Instead, try to find a solution
> that uses set-based queries.
>
> But this brings up several questions / senarios:
>
> * I created several stored procedures that take parameters and inserts
> the data into the appropriate tables. This was done for easy access/use
> from client side apps (i.e. web-based).
>
> Proper development tactics says to try and do "code reuse". So, if I
> already have stored procs that do my logic, should I be writing a
> second way of handling the data? If I ever need to change the way the
> data is handled, I now have to make the same change in two (or more)
> places.
If a SP encapsulates a plain INSERT I'd throw it out. If there is more
complex logic involved I'd probable leave it in.
> * Different data from the same row needs to be inserted into multiple
> tables. "Common sense" (maybe "gut instinct" is better) says to handle
> each row as a "unit". Seems weird to process the entire set for one
> table, then to process the entire set AGAIN for another table, and then
> YET AGAIN for a third table, and so on.
This smells like a design issue. If you have to insert the exact same
data into multiple tables chances are that your table layout is flawed.
> * Exception handling. Set based processing means that if one row fails
> the entire set fails. Looping through allows you to fail a row but
> allow everything else to be processed properly. It also allows you to
> gather statistics. (How many failed, how many worked, how many were
> skipped, etc.)
Normally you want all or nothing. This one sounds as if you placed
business logic into to database. This might or might not be a good idea
but there seems to be a chance that this is better done in a middle tier
(for example because it eases porting to another RDBMS). But it depends
on the error handling and operations you do in SP's.
> ?? Good idea ?? The alternative is to create a temporary table (sandbox
> or workspace type thing), copy the data to there along with "status" or
> "valdation" columns, run through the set many times over looking for
> any rows that may fail, marking them as such, and then at the end only
> dealing with those rows which "passed" the testing. Of course, in order
> for this to work you must know (and duplicate) all constraints so you
> know what to look for in your testing.
I wouldn't recommend one or the other direction with so few info.
Kind regards
robert
Navigation:
[Reply to this message]
|