Advantages and disadvantages of Stored Procedures

Advantages and disadvantages of stored procedures:

Advantages of stored procedures:
1. The T-SQL Process Code becomes more complex as the application changes over time, adding or deleting functionality, and StoredProcedure provides a replacement location for encapsulating this code.

2. execution Plan (the stored procedure will be compiled at the first run, this generates an execution plan-a record of the steps that Microsoft SQL Server must take to get results specified by the T-SQL during the stored procedure .) Cache improves performance.
But in the new version of SQL server, execution plans have been cached for all T-SQL batches, and no matter whether they are stored or not.

3. stored procedures can be used to reduce network traffic. Stored Procedure Code is directly stored in the database, so it does not generate a large amount of code traffic for T-SQL statements.

4. Using Stored Procedures enables you to enhance the reuse of execution plans, thereby improving performance by using Remote Procedure Call (RPC) to process stored procedures on the server. RPC encapsulates parameters and calls server-side processes so that the engine can easily find matching execution plans and insert updated parameter values.

5. high maintainability. Updating stored procedures usually requires less time and effort than changing, testing, and re-deploying an assembly.

6. Simplified and consistent code. A stored procedure can be used in different places of application code.

7. Better Version Control: By using Microsoft Visual SourceSafe or another source code control tool, you can easily restore or reference older versions of stored procedures.

8. Enhanced Security:
A. By granting users access to stored procedures (rather than tables), they can provide access to specific data;
B. Improve code security and prevent SQL injection (but not completely resolved, for example, attaching data operation language DML to input parameters );
C. The SqlParameter class specifies the Data Type of stored procedure parameters. As part of a deep defensive policy, it can verify the value type provided by the user (but it is not foolproof, should be passed to the database for additional verification ).

Disadvantages of stored procedures:

1. if you need to change the input stored procedure parameters or change the data returned by the stored procedure, you still need to update the code in the Assembly to add parameters and update GetValue () call, and so on. At this time, it is complicated.

2. Poor portability

Because stored procedures bind applications to SQL Server, using stored procedures to encapsulate business logic limits the portability of applications. If the portability of applications is very important in your environment, it may be a better choice to encapsulate the business logic in an RDBMS-specific middle layer.

3. the fatal disadvantage of using a large number of stored procedures for business logic development is that many stored procedures do not support object-oriented design and cannot encapsulate the business logic in an object-oriented manner, thus, a general business logic framework that supports reuse cannot be formed.

4. The code is quite difficult to maintain due to poor readability.