Advantages and disadvantages of Stored Procedures

Advantages and disadvantages of stored procedures:

Advantages:
1. ApplicationProgramChanging over time, adding and deleting features, T-SQL ProcessesCodeStoredprocedure 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, the execution plan has been cached for all T-SQL batches, regardless of whether they are in the stored procedure, so there is no advantage.

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 anotherSource codeControl tool, you can easily restore to or reference the old version of the stored procedure.

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:

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.