Opinions About Insertion Technique

Looking for some insight from the professionals about how they handle
row inserts. Specifically single row inserts through a stored
procedure versus bulk inserts.

One argument are people who say all inserts (and updates and deletions
I guess) should go through stored procedures. The reasoning is that
the developers that code the client side have no reason to understand
HOW the data is stored, just that it is. Another problem is an insert
that deals with multiple tables. It would be very easy for the
developer to forget a step. That last point also applies to business
logic. In my case, adding a security to our SecurityMaster can touch 1
to 4 tables depending on the type of security. Also, certain fields
are required while others are set to null for depending on the type.

Because a stored procedure cannot be passed datasets but only scalar
values, when you need to deal with multiple (i.e. bulk) rows you are
stuck using cursors. This post is NOT about the pros and cons of
cursors. There are plenty of those on the boards (some of them
probably started by me and showing my understanding (or more
correctly, lack of) of the way to do things). Stored procedures also
give you the ability to abort and/or log inserts that cannot happen
because of contraints and/or business rule failures.

Another approach is to write code (not accessible from outside the
database) that handles bulk inserts. You would need to write in rules
to "extract" or "exclude" rows that do not match constraints or
business rules otherwise ALL the inserts would fail because of one bad
row. I guess you could put the "potential" rows into a temp table.
Apply your rules to the temp table and delete / move rows that would
fail. Any rows left can that be bulk inserted. (You could also use the
rows that were moved to another temp table for logging why they
failed.)

So that leaves use with two possible ways to get data into the system.
A single row based approach for client apps and a bulk based for
internal use. But that leaves use with another problem. You now have
business logic in TWO separate areas. You have to remember to modify
code or fix bugs in multiple locations.

For those that are still reading my post, my question is...

How do you handle this? What is the approach you take?