Differences between stored procedures and SQL statements pros and cons

For:

Advantages and disadvantages of stored procedures:

Advantages:

1. The T-SQL process code becomes more complex as the application changes over time, adding and removing functionality, and StoredProcedure provides a replacement location for encapsulating this code.

2. Execution plan (the stored procedure will be compiled on first run, which results in an execution plan-in fact, a record of the steps Microsoft SQL Server must take to get the results specified by T-SQL in the stored procedure. ) cache to improve performance.

... but 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 and stored procedure code is stored directly in the database, so there is no code traffic 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 calls (RPC) to process stored procedures on the server. The RPC encapsulation parameters and the way the server-side procedure is invoked make it easy for the engine to find a matching execution plan and simply insert the updated parameter values.

5. With high maintainability, updating stored procedures typically requires less time and effort than changing, testing, and redeploying assemblies.

6. The code is streamlined and consistent, and a stored procedure can be used in different locations of the application code.

7. Better version control, by using Microsoft Visual SourceSafe or some other source control tool, you can easily revert to or reference an older version of a 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, prevent SQL injection (but not completely resolved, for example, the data manipulation language--dml, attached to the input parameters);

The C, SqlParameter class specifies the data type of the stored procedure parameter, which, as part of a deep-seated defensive strategy, validates the user-supplied value type (but is not foolproof or should be passed to the database for additional validation).

Disadvantages:

1. If you change the scope 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 more tedious to estimate.

2. Portability is poor

Because stored procedures bind applications to SQL Server, using stored procedures to encapsulate the business logic limits the portability of the application. If the portability of your application is important in your environment, encapsulating the business logic in an RDBMS-neutral middle tier might be a better choice.

SQL statements are flexible, portable, and query slower than stored procedures

Differences between stored procedures and SQL statements pros and cons