1

An answer to this question states that "Ideally, though, you would not allow ad hoc DML against your tables, and control all DML through stored procedures."

Why is this ideal? What problem does this solve versus GRANTing SELECT, UPDATE, INSERT, DELETE to the tables which a user needs to manipulate?

Community
  • 1
  • 1
jl6
  • 6,110
  • 7
  • 35
  • 65
  • 1
    It's not "ideal" not even close. There are no absolutes in development. – zerkms Oct 23 '14 at 10:15
  • The opposite of ad-hoc DML doesn't necessarily mean stored procedures. – DavidG Oct 23 '14 at 10:18
  • Stored procedures can be tested in isolation from the code. They can be optimised with cached plans, leading to quicker execution. They are parametrized by default, protecting you from sql injection. They can be referenced from other code in your database if required. Changes within stored procedures don't require code recompilation... – Tanner Oct 23 '14 at 10:48

3 Answers3

5

Views and stored procedures are like an API. They allow you to hide the implementation, version changes, provide security and prevent unfortunate client operations like "get all fields from all invoices since the company started".

Views and stored procedures allow the DBA to modify the schema to meet performance requirements without breaking applications. The data may need to be partitioned horizontally or vertically, split across files, fields may have to be added or removed etc. Without stored procedures, these changes would require extensive application changes.

By controlling the stored procedures and views each application uses you can provide versioning of schema changes - each application will see the database API it expects.

Permissions can also be assigned on specific stored procedures, allowing you to restrict what users can do without giving them full access to a table. Eg, you could allow regular employees to change only their contact details on the employee table but allow HR to make more drastic changes.

Additionally, you can encapsulate complex data-intensive operations in a single testable procedure, instead of managing raw SQL statement strings inside a client's source code.

Stored procedure execution can also be tracked a lot easier with SQL Server Profiler or with dynamic management views. This allows a DBA to find the hotspots and culprits for possible performance degradation.

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
  • That is well said and demosntrates a total ignorance for the item called "money". Because this approach also pretty much forces a MUCH large development budget into the development. Obviously not a problem for some, but agility and time to market and money per function are quite more important in most businesses. – TomTom Oct 23 '14 at 10:58
  • @TomTom I think the opposite is true. The cost of rewriting an application can be so big that smaller teams are afraid to fix bad schemas, preferring to cover up problems until they are forced to fix them (typically by a meltdown). The result is that the budget gets wasted on support issues rather than producing new code and features. A project's life (and costs and budget) doesn't end when the CD gets burned – Panagiotis Kanavos Oct 23 '14 at 11:26
1

I would believe this follows the general idea that actions should have defined allowable input and return the expected output. The idea of ad-hoc changes to the data or database structure poses risk on several levels, but may be necessary based on what is required.

zerkms noted in the comments: there are no absolutes, only best practices. As a best practice, if you can correctly scope the intended outcomes and restrict users and processes to only necessary actions and permissions, you should for the safety and integrity of the system.

Vinnie
  • 3,889
  • 1
  • 26
  • 29
1

There are a few solid reasons for why you need to use Stored procedure

  1. Stored procedures are compiled once and stored in executable form, so procedure calls are quick and efficient. Executable code is automatically cached and shared among users.

  2. Stored procedure avoids SQL injection.

  3. A set of queries in a stored procedure is executed with a single call. This minimizes the use of slow networks, reduces network traffic, and improves round-trip response time(for example in the case of bulk inserts).

Sarath Subramanian
  • 20,027
  • 11
  • 82
  • 86