Analysis of the advantages and disadvantages of stored procedures

Advantages and disadvantages of stored procedures <span class="Apple-converted-space"></span> :





Stored Procedure benefits:


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 is compiled the first time it is run, which produces an execution plan-is actually a record of the steps that Microsoft SQL Server must take to get the results specified by T-SQL in the stored procedure.) ) Cache improves performance.  


but a new version of SQL Server, the execution plan has been <span class="Apple-converted-space"></span> cached for all T-SQL batch , regardless of whether they are in the stored procedure, so there is no comparative advantage.  





3. Stored procedures can be used to reduce network traffic, stored procedure code is stored directly in the database, so no code traffic for a large number of T-SQL statements is generated.  





4. Using stored procedures enables you to enhance the reuse of execution plans, thereby improving performance by using remote Procedure Call (RPC) to handle stored procedures on the server. The RPC encapsulation parameters and the way the server-side process is invoked make it easy for the engine to find a matching execution plan and simply insert the updated parameter values. 





5. High maintainability, updating stored procedures typically takes less time and effort than changing, testing, and redeploying assemblies.  





6. Consistent code simplification, a stored procedure can be used in different locations of application code.  





7. Better version control, you can easily revert to or reference older versions of stored procedures by using Microsoft Visual SourceSafe or some other source control tool.  





8. Enhanced security:


A, they can provide access to specific data by granting users access to stored procedures (rather than tables);


B, improve code security, prevent SQL injection (but not completely resolved, for example, the data manipulation language--dml, attach to input parameters); <span class="Apple-converted-space"></span>

The
C, SqlParameter class specifies the data type of the stored procedure parameter, as part of a deep-seated defensive strategy that validates the type of value provided by the user (but is not foolproof or should be authenticated before being passed to the database).  





Stored procedure disadvantage:



1. If the scope of the change is large to make changes to the parameters of the input stored procedure, or to change the data returned by it, you still need to update the code in the assembly to add parameters, update GetValue () calls, and so on, which is tedious to estimate.  





2. Portability Difference





because stored procedures bind applications to SQL Server, using stored procedures to encapsulate business logic restricts application portability. If application portability is important in your environment, it may be a better choice to encapsulate business logic in a middle tier that is not specific to an RDBMS.  





3. Development of business logic with a large number of stored procedures the fatal disadvantage is that many stored procedures do not support object-oriented design and cannot encapsulate business logic in an object-oriented manner, thus making it impossible to form a common business logic framework that supports reuse.  





4. The code is poor in readability and quite difficult to maintain.