If a non-parameterized SQL query takes 2 seconds to complete, it's also "eating resources" from other processes that "expect immediate response" as well.

But he's referring to data munging being done at the client, not at the server. An extra second on each of 50 workstations- so what. 50 workstations all running 1-second munging SP at once on the server... whoops.

Unless you are using sp_executesql with parameterized queries, well written stored procedures will run faster than ad hoq sql.

But if we assume that both have been crafted by equally competent practitioners, SP will not run measurably faster?

But with increasing complexity, the proc generally performs better.

Definitely not for Edits. For very large tables with (for example) a random number of fields changed by a user, SP can be expected to consume far more resource at the server end- either because numerous additional parameters have been passed, or because massive extra processing is required to interpret the NULL placeholders.

The efficiency benefits aren't as big as if you are using *properly* parameterized SQL statements and queries. If you're not *properly* parameterizing the statements, however, the performance difference is going to be significant.

That's the third time you've made that point. Why is it helpful to compare properly constructed SP with improperly constructed SPT? The valid comparison would be a SP writer who concatenates a SQL string to include passed variables and executes it.

The real advantages to stored procedures is that you can encapsulate many statements in them and treat them as a unit, and they prevent sql injection.

The first is definitely true and could be a BIG advantage of SP in 2006 IMHO. The second does not favor you- preventing injection is TOTALLY AUTOMATIC using parameterised SPT or RV from VFP, and has been since 1995. Whereas it is easy (and surprisingly common) for SP to use concatenation, creating exposure to injection.

The precompilation is still an advantage on state of the art machines today.

Not a measurable advantage.

As I stated before, this is a total non-issue for those who have scripts to generate these SPs.

Are you saying you have scripts that automatically recreate all your SP to match changed table structure?

There was also a reaction from someone else along the lines of ‘900 stored procedures is a lot’. If that's too much to swallow, then a .NET framework with dozens of namespaces containing over 2,500 classes and methods is likely to be too much for you as well.

That someone was me and you have shifted the context. Are you still claiming that you don't insult people? ;-)

/regarding SPT more flexible than SP/ Tonight I spoke to some SQL MVPs and a technology director. Unanimously, they disagreed with the statement as well, especially on all but the most simple queries.

It's the other way around- less difference for simple queries, heaps more flexibility using SPT once you start doing complex queries. Example- when creating my SELECT for SPT, I add an extra UNION. How will you do it in your SP? I add an extra table so I can rank according to sales volume. How will you do it in your SP? It seems a truism that SPT allows ad hoc queries to be crafted on the fly, but SP does not. How can SP possibly be more flexible?

but from what I’ve heard and experienced, SQL injection, dealing with large amounts of t-sql code and treating a proc like any other objects make them a better code management choice.

No validity to the Injection claim- see above. Dealing with large amounts of t-sql code... not sure what you mean. Treating SP like any other object- yes, I can see why that might be an advantage. Thanks for that!

The problem of course if that when you have a maintenance problem you 'could' have undisciplined programmers, and it isn't necessarily an SPT vs SP argument. Rather undisciplined programmers vs people who take coding to be a craft. This really gets back to the notion, supported by the majority of people who make these decisions, that SPs are a better code management choice.

If the "argument" boils down to SP being better because programmers are undisciplined and you need to protect critical code from them, it isn't a technical argument at all. ;-)

Walter, for the most part, the arguments you've made against stored procedures are either weak, or just plain bad. I know for a fact that people who would not let you touch their databases if you advanced the same arguments to them.

you act as if you've demolished his arguments, but you haven't. Your points about injection and flexibility are demonstrably wrong and you rely on improperly constructed SPT to rebut the argument re performance.

The strong points you do make are that treating SP as just another object may be an advantage, and that if you're dealing with undisciplined programmers it helps to lock database access away so they cannot screw it up.

Is that it?

I'm a student of such SQL authors as Ken Henderson, Itzik Ben-Gan, Joe Celko, Dan Sullivan, and Bob Beauchemin. So if you believe I'm spreading bad advice, then feel free to share that information with them, because you're essentially saying the same thing to them.

Puh-leez.

Or better yet, as I've said before, venture from the safe confines of the UT and post your views on a SQL Server forum.

Sheesh, I got all sorts of stick for daring to raise the issue of autospanning cursors right here, so he'd have to be a fool to go somewhere where you make it sound as if everybody knows they are right, apparently don't know WHY they are right, but certainly know how to bully and attack any heathen who dares to suggest their emperor has no clothes.