Couldn't agree more Dragan and the "it depends" can then change from individual to individual, just cause someone that has a name in the community say it's best to do SP, you shouldn't start doing it for every call u need to do to the database, you should think for your self and see when is right to use them.
I can't still understand why so many people say that you should use SP's to insert, update, delete records from the database, doesn't make any sense to me, SP are useful especially when you have do process allot of data and the final result will be a value or a cursor returned to the client, then it makes sense to have a SP so you don't have the latency of the roundtrips between the client and the server.
Now have a SP that just do a SELECT statement in my humble opinion is a waist of time and it doesn't add any value to the application.
just my 1.6 cents;) that should be around the 2 cents of a dollar
>>>---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".
Alexandre Palma
Senior Application Architect