What are the pros and cons to keeping SQL in Stored Procs versus Code
An amount be appropriate scenario when saved methods ought to be used?
I happened upon implementation where almost whole data manipulation was handled by store methods, even easiest type of Place/Remove claims were wrapped and used via SP's.
So, what is the rationale for implementing saved methods generally?
Sorry for this type of beginners question..
Two reasons I understand of:
The saved methods feel at ease from attacks for example SQL injection attacks
Saved methods are occasionally precompiled making execution faster.
Outwith the reason why @Tom has stated that are most likely the most crucial (Speed/Security) I'd also state that one other good reason to make use of Saved Methods is code re-use. When you are writing exactly the same SQL everywhere its often a sign that it ought to be a saved procedure. Also, one other good reason could it be enables not just designers, but DBA's a chance to change/add new methods if needed.
for me personally it's the same question as if you should produce a function/method while programming.
For instance when the functionality is required to be repeated in lots of places, or will probably be known as more often than once then it ought to be inside a function.
It enables you to definitely keep data access close to the data. I've labored in systems where all data access was saved procs with server side wrapper functions. It had been pretty clean ( although not as 'cool' being an ORM )
When others have to access your computer data and you have to offer an API in the database - Procs will be a way to permit you treatments for what/the way they can get on.
I'm responding to from a company perspective.
two kinds of designs,
- InvestOrthe majority of business logic on DB server
- InvestOrthe majority of Business logic on application server.
In #1, you utilize Saved procedure to implement the application logic rather than the programming language. In #2, you apply the programming language to implement the logic, which is simpler to debug and permit code reuse and all sorts of additional features that any programming language provides.
If you're a DB guru (as well as your application is mid to small), pick the first approach, otherwise choose second approach.
BTW, you'll find .Internet application uses the very first approach, and Java application adopted the 2nd.