What are the advantages and disadvantages of stored procedures?

Advantages:

1. because the application Program is constantly changed over time, the deletion function is added, the T-SQL Process Code becomes more complex, 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 no code traffic for T-SQL statements is generated.
4. Using Stored Procedures allows 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 locations of application code.
7. for better version control, use Microsoft Visual sourcesafe or another Source Code control tool, you can easily restore or reference stored procedures of the old version.
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, encapsulating business logic Using Stored Procedures 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.