Dragan,
i think that the best thing is to use SP's for inserting data that done very often but non SP queries for queries on the files. the scenario i have is that we write away transactions, some customers post up to 2000 a day. 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?
~M
>>>---Using stored procs is considered a "best practice".
>>
>>A mix of stored procs and parameterized ad-hoc sql and business logic in the middle tier is IMO "best".
>
>Thanks, this was the best way to say what I wanted to say :).
>
>I've done some pretty heavy SP in my time, and it worked great - because the task at hand was to do some heavy data crunching without any need for anything outside the DB.
>
>OTOH, I've seen some SPs where I had my serious doubts whether they'd be the right approach. It was about a string search, where there were five almost identical SPs, to handle the case when there was one word to search for, or two, ... or five. Which is IMO ridiculous, I'd rather have just one SPT call composed programmatically (but then the client side was done in Java - and I wonder whether those guys were able to compose a SPT call on-the-fly). For more complicated searches, with more fields involved, the TSQL just doesn't provide the flexibility, not that I know of, to compose an ad hoc query, not such that we can easily knit in 10 minutes in VFP.
>
>I'd conclude the answer to "SP - yes or no" is the classical "it depends".
Go raibh maith agat
~M