|
|
Posted by David Portas on 10/01/76 11:43
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.
>
> * 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.
>
> * 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.)
>
> ?? 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.
Another reason why code re-use is less of an issue in SQL compared to
general programming languages is that it's so easy to generate scripts
automatically from your database's metadata. That's particularly the
case for CRUD scripts, transformation scripts, archiving, auditing,
etc. If you have good constraints and naming conventions then you can
automate the production of thousands of lines of code in seconds. That
maybe not as important as Robert and Hugo's other sensible comments but
it is a distinct advantage.
--
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]
|