Why (and when to) use stored procedures?

Question

Possible Duplicate:
What are the pros and cons to keeping SQL in Stored Procs versus Code

What would be appropriate scenario when stored procedures should be used?

I stumbled upon implementation where almost whole data manipulation was handled by store procedures, even simplest form of INSERT/DELETE statements were wrapped and used via SP's.

So, what's the rationale for using stored procedures in general?

Sorry for such a beginners question..

Answer 1

Two reasons I know of:

1. Security

The stored procedures are secure from attacks such as SQL injection attacks

1. Speed

Stored procedures are sometimes precompiled which makes execution faster.

Answer 2

for me it is the same question as whether or not to create a function/method while programming.

For example if the functionality is needed to be repeated in many places, or is going to be called more than once then it should be in a function.

Answer 3

It allows you to keep data access near the data. I have worked in systems where all data access was stored procs with server side wrapper functions. It was pretty clean ( but not as 'cool' as an ORM )

Answer 4

Outwith the reasons @Tom has already pointed out which are probably the most important (Speed/Security) I would also say that another good reason to use Stored Procedures is code re-use. If you find yourself writing the same SQL all over the place its usually a sign that it should be a stored procedure. Also, another good reason is it allows not only developers, but DBA's the ability to change/add new procedures if required.

Answer 5

When other systems need to access your data and you need to provide an API at the database - Procs would be a way to allow you control over what/how they access it.

I am answering from an enterprise perspective.

Answer 6

two types of designs,

1. Put all/most of business logic on DB server
2. Put all/most of Business logic on application server.

In #1, you use Stored procedure to implement your application logic instead of the programming language. In #2, you use the programming language to implement the logic, and it is easier to debug and allow code reuse and all other features that any programming language provides.

If you are a DB guru (and your app is mid to small), choose the first approach, otherwise choose second approach.

BTW, you will find .NET app uses the first approach, and Java app followed the second.