Are Stored Procedures Evil?

Question

Are Stored Procedures Evil?

Tim OPry

SSCrazy

Points: 2988
More actions
April 17, 2008 at 6:27 am

#186567

I am working on a MySQL project and in doing some research, ran across this site/article. I must admit that I am one of those that uses stored procedures whenever possible - triggers though, only when absolutely necessary (which is rare for me).
I did think he made several interesting points and would be interested in what some of you think:

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Peter Schofield Ten Centuries Points: 1364 More actions · Answer 1

Can't access the URL, but any programming technique described as "evil" is umerited - it's about appriopriate use.

Stored procedures in SQL Server are the primary way to go for data access & manipulation for performance, security, and modularity.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 2

Peter Schofield (7/3/2008)
Stored procedures in SQL Server are the primary way to go for data access & manipulation for performance, security, and modularity.

That's a great thing to say... but, even though I agree with you, you've not said why nor offered any type of proof. 😉 Would you elaborate, please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 3

Tim OPry (4/17/2008)
I did think he made several interesting points and would be interested in what some of you think:

My personal feeling is that Tony just doesn't like writing stored procedures. If you google well enough, you'll find contrary posts to everything he said.... and vice versa.

You said you use stored procedures... why? Your reasons for using them are probably as valid or more valid than his reasons for not using them.

I refer them because of the following...

1. Because of the way they use parameters, they are much more resistant to SQL Injection attacks than many other methods especially embedded dynamic SQL.

2. In high hit scenarios, properly written stored procedures can be made to cache and reuse the execution plan... many forms of dynamic or externally provided SQL cannot be made to do so.

3. I'd much rather alter a stored procedure than recompile an app, even if it is just middle-ware.

4. Folks that aren't skilled at writing stored procedures shouldn't be writting SQL in another language to access the database because they still don't have the skill to do so especially when it comes to performance and scalability.

I could go on, but those are just my opinions... diametrically opposite of that which Tony offered, but still just an opinion. Googling will also get you lot's of opinions. The best thing to do is to search the documentation for MySql for the advantages of Stored Procedures.

And, although nothing in SQL is totally evil, there are things that are 99% evil... most have to do with RBAR in one form or another. Yes, I agree... implementation dependent and all that. What I've found is that a lot of people don't know when to use what... and that makes some of those things evil 99% of the time. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

...it was never the arrows but the indian. 😀

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.