Analysis of the advantages and disadvantages of stored procedures _mssql

Advantages and disadvantages of stored procedures:

Stored Procedure benefits:
1. Since the application changes over time, adding and removing functionality, the T-SQL process code becomes more complex, and StoredProcedure provides a replacement location to encapsulate 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.
However, 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 comparative advantage.

3. Stored procedures can be used to reduce network traffic and stored procedure code is stored directly in the database, so no code flow is generated for a large number of 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 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 versioning, 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);
C, the SqlParameter class specifies the data type of the stored procedure parameter, and as part of a deep-seated defensive strategy, you can verify that the user-supplied value type (but not foolproof, or should be passed to the database for additional validation).

Stored Procedure disadvantage:

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

2. Poor portability

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 very difficult to maintain.