Could it be a best practice to make use of SP (store Proc) for every SQL get in touch with .Internet programs?
Could it be urged for performance reasons and also to reduce area for SQL injection attacks (in web programs)?
Saved methods possess a couple of advantages over parameterized queries:
When used solely, you are able to switch off CREATE, Place, Choose, UPDATE, ALTER, DROP, Remove, etc access for the application accounts, and by doing this add a tiny bit of security.
They offer a regular, workable interface if you have multiple programs utilizing the same database.
Using methods enables a DBA to handle and tune queries despite a credit card applicatoin is used.
Implementing small changes and bug fixes is a lot simpler.
They likewise have a couple of disadvantages:
The amount of methods can rapidly grow to the stage where maintaining them is tough, and current tools don't give a simple way of sufficient documentation.
Parameterized queries place the database code near the place where it's used. Saved methods ensure that it stays far separated, making finding related code harder.
Saved methods are not as easy to version.
You will need to weigh individuals costs/benefits for the system.
Should you send your queries to SQL Server as parameterized queries, SQL Server will cache the execution plan And can sanitize your parameter inputs correctly to prevent SQL injection attacks.
I favor saved procs over inline SQL, because by doing this the SQL is a consolidated place however, I favor utilizing a tool like nHibernate that will auto create the SQL for me personally, then you've no SQL to bother with!
There's yet another advantage - if this involves tuning, especially per customer, it may be easily completed with SP (with the addition of hints as well as spinning the code). With embedded SQL it's difficult.
It's another way of doing things. Benefits include keeping all of your SQL code in one location, procs being verified for syntax at creation time, or being able to set permissions on procs, which often represent some type of "action" and therefore are suitable to some conceptual security model.
Disadvantages include massive amounts of procs for just about any medium or bigger application, and all sorts of the housekeeping that accompany that.
My employer's product uses procs for everything, and I have to admit using the right practices in position the correct answer is manageable.