How stored procedures and SQL statements are selected

Advantages of applying stored procedures:
1. Better performance
Stored procedures are precompiled, compile only at creation time, and do not need to be recompiled for each subsequent execution of the stored procedure.
Typically, SQL statements are compiled once per execution, so using stored procedures can improve database execution speed.
2. More flexibility in function implementation
In the stored procedure can apply the condition judgment and the cursor and so on the statement, has the very strong flexibility, may call directly the database some built-in function, completes the complex judgment and the more complex computation.
3. Reduce network transmission
Complex business logic requires multiple SQL statements, and when there are many operations between the client and the server,
Will generate a lot of network transmissions. If these operations are placed in a stored procedure, the network transport between the client and the server is reduced and the network load is reduced.
4. Better security
(1) Database administrators can better control permissions, stored procedures can block direct access to the underlying database objects, using EXECUTE permissions to invoke stored procedures, without having access to the underlying database objects explicit permissions.
(2) When a procedure is called over a network, only calls to the execution procedure are visible. The table and database object names cannot be seen, and SQL statements cannot be embedded to help avoid SQL injection attacks.
The drawbacks of stored procedures:
1. The architecture is not clear enough to object-oriented
Stored procedures are less suited to object-oriented design and cannot be encapsulated in an object-oriented manner.
The business logic is implemented in the storage layer, which increases the coupling between business and storage, and the readability of the code is reduced.
2. High development and maintenance requirements
The writing of stored procedures is directly dependent on the developer, and if the business logic is changed more frequently, the database needs to be directly manipulated.
A large number of business dimension to the database, a lot of exceptions can not be captured in the code, the problem is more difficult to troubleshoot, need the help of database administrators.
3. Portability is poor
Excessive use of stored procedures can reduce the portability of the system. Some additional work may be added when the storage is related to scaling.
How the stored procedure and SQL statements choose:
In common project development, it is not recommended to use stored procedures extensively, compared with SQL statements, stored procedures are suitable for complex business logic, time consuming, and less requests, such as background large batch query, periodic update and so on.

(1) When a transaction involves multiple SQL statements or involves operations on multiple tables, you can consider applying stored procedures
(2) The application of stored procedures can be considered when the completion of a transaction requires complex business logic
(3) More complex statistics and summaries can consider applying background stored procedures

How the stored procedure and SQL statements are selected