Mark,
this transaction updates a header file, detail line (one record for every item bought) a stock file, a stock history file and a couple of others depending on customer needs. would a single SP be the best way to write away this trans or would it be better to use a SP for each table to be updated?You're going to want to wrap the whole thing in a SQL Transaction. I'd lean towards a mega-SP for this, but there will be some complexity because of the 1-M detail lines. So for a start you could consider using the SQLSetProp() on your connection to set transactions to manual, do all your inserts, then SQLCommit() at the end. Long term you might want to define an XML or similar message API to submit to a middle tier or SP. That's what we do.
"... They ne'er cared for us
yet: suffer us to famish, and their store-houses
crammed with grain; make edicts for usury, to
support usurers; repeal daily any wholesome act
established against the rich, and provide more
piercing statutes daily, to chain up and restrain
the poor. If the wars eat us not up, they will; and
there's all the love they bear us."
-- Shakespeare: Coriolanus, Act 1, scene 1
