Some benefits and precautions for using Stored Procedures

ArticleDirectory

• Performance
• Maintainability and abstraction
• Security
• Does Stored Procedure apply to me?
• Use set nocount on
• Do not use SP _ prefix
• Use as few optional parameters as possible
• Use output parameters whenever possible
• Return Value
• Use DDL first, and then DML
• Always use comments

Stored procedures are used every day. The debate on SQL statements used in stored procedures is also ongoing. I personally think that using stored procedures is better than using SQL statements:

Stored procedures are encapsulated by some SQL statements and control statements. They reside in databases and can be applied by customers.ProgramCan also be called from another process or trigger. Its parameters can be passed and returned. Similar to function procedures in applications, stored procedures can be called by name, and they also have input and output parameters.

Depending on the type of the returned value, we can divide the stored procedure into three types: the stored procedure of the returned record set, the stored procedure of the returned value (also known as the scalar stored procedure), and the behavior stored procedure. As the name suggests, the execution result of the stored procedure that returns the record set is a record set. A typical example is to retrieve records that meet one or more conditions from the database; after the stored procedure of the returned value is executed, a value is returned. For example, a function or command with a returned value is executed in the database. Finally, the stored procedure is only used to implement a function of the database, there is no return value, for example, update or delete operations in the database.

Benefits of using Stored Procedures

Compared with using SQL statements directly, calling stored procedures in applications has the following benefits:

(1) reduce network traffic. The network traffic for calling a stored procedure with few rows of data may not be significantly different from that for directly calling SQL statements. However, if the Stored Procedure contains hundreds of rows of SQL statements, therefore, its performance is definitely much higher than that of a single SQL statement.

(2) Faster execution. There are two reasons: first, the database has been parsed and optimized when the stored procedure was created. Second, once the stored procedure is executed, a stored procedure will be retained in the memory, so that the next time you execute the same stored procedure, you can directly call it from the memory.

(3) better adaptability: Because stored procedures access databases through stored procedures, therefore, database developers can make any changes to the database without modifying the stored procedure interface, without affecting the application.

(4) protocol work: the coding work of applications and databases can be performed independently without mutual suppression.

Description on msdn

Considerations for using Stored Procedures

Maybe you have written a T-SQL that uses the sqlcommand object in multiple places, but you have never considered whether there is a ratio to incorporating it into data accessCodeBetter location. Because the application adds some functionality over time, it may contain some complex T-SQL Process Code internally. Stored Procedures provide a replacement location for encapsulating this code.

Most people may already know stored procedures, but for those who do not know stored procedures, Stored Procedures refer to a group of T-SQL statements that are stored together in the database as a single unit of code. You can use input parameters to pass in runtime information and retrieve data that is used as a result set or output parameter. 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 a stored procedure. Then, the execution plan is cached in the memory for future use. This will improve the performance of stored procedures, because SQL server does not need to re-analyze the code to determine how to process it, but simply reference the cache plan. The cache plan remains available until SQL server restarts or until it overflows memory due to low usage.

Performance

The cache execution plan has made the stored procedure more advantageous than the query. However, for several latest versions of SQL Server, the execution plan has been cached for all T-SQL batches, regardless of whether they are stored in the stored procedure. Therefore, the performance based on this function is no longer a selling point of stored procedures. Any T-SQL batch processing that uses static syntax and is committed enough to prevent execution plan overflow of memory will have the same performance benefit. The "static" part is critical; any changes, even insignificant changes such as adding comments, will lead to a failure to match the cached plan, and thus will not be able to reuse the plan.

However, stored procedures can still provide performance benefits when they can be used to reduce network traffic. You only need to send ExecuteStored_proc_nameStatement, not the entire T-SQL routine, which can be widely used in complex operations. A well-designed stored procedure can simplify many round-trips between the client and the server into a single call.

In addition, stored procedures allow you to enhance reuse of execution plans, thereby improving performance by using Remote Procedure Call (RPC) to process stored procedures on the server. When storedprocedure's sqlcommand. commandtype is used, the stored procedure is executed through rpc. RPC encapsulates parameters and calls server-side processes so that the engine can easily find matching execution plans and insert updated parameter values.

Consider using stored procedures to improve performance, and finally consider whether to make full use of the advantages of T-SQL. Consider how to process data.

•	Do you want to use collection-based actions or perform other actions that are fully supported in the T-SQL? Therefore, the stored procedure is an option, and the inline query can also be used.
•	Are you sure you want to perform row-based operations or complex string processing? You may have to reconsider this kind of processing in the T-SQL, which does not include using stored procedures, at least after being published in Yukon and available in the Common Language Runtime Library (CLR) integration.

Maintainability and abstraction

Another potential advantage to consider is maintainability. Ideally, the database architecture is never changed and the business rules are not modified, but in the real environment, the situation is completely different. In this case, you can modify the stored procedure to include data in the new x, y, and z tables to support new sales activities, instead of changing this information somewhere in the application code, maintenance may be easier for you. Changing this information during stored procedures makes updates transparent to applications-you still return the same sales information, even if the internal implementation of the stored procedure has been changed. Updating stored procedures usually requires less time and effort than changing, testing, and re-deploying an assembly.

In addition, through abstraction and saving this code in the stored procedure, any application that needs to access data can obtain consistent data. You can obtain consistent information without maintaining the same code in multiple locations.

Another maintainability advantage of storing T-SQL in stored procedures is better version control. You can perform version control on the scripts used to create and modify stored procedures, just as you canSource codeThe module performs version control. By using Microsoft Visual sourcesafe or another source code control tool, you can easily recover to or reference older stored procedures.

When using stored procedures to improve maintainability, it is worth noting that they cannot prevent you from making any possible changes to your architecture and rules. 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 () and so on.

Another issue that should be noted is that because stored procedures bind applications to SQL Server, encapsulating business logic using stored procedures will restrict 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.

Security

The final reason to consider using stored procedures is that they can be used to enhance security.

To manage users' access to information, users can access specific data by granting them access permissions to stored procedures (rather than basic tables. You can regard stored procedures as the SQL Server View (if you are familiar with them) unless stored procedures accept user input to dynamically change the displayed data.

Stored procedures can also help you solve code security issues. They prevent some types of SQL insert attacks-mainly attacks that use operators (such as and or) to attach commands to valid input parameter values. When an application is attacked, the stored procedure can also hide the implementation of business rules. This is important for companies that regard such information as intellectual property rights.

In addition, stored procedures allow you to use the sqlparameter class provided in ADO. Net to specify the Data Type of stored procedure parameters. This provides a simple method to verify the value type provided by the user (as part of a deep defensive policy. Parameters are as useful in inline queries as in Stored Procedures in narrowing down the acceptable range of user input.

When using stored procedures to enhance security, you must note that poor security or coding practices will still attack you. If you do not pay attention to the creation and allocation of SQL Server roles, people will access data that should not be seen. At the same time, if you think that using stored procedures can prevent all SQL insert code attacks (for example, attaching data operation language (DML) to input parameters), the consequences will be the same.

Also, parameter-based data type verification is not foolproof whether the T-SQL is in the code or stored procedure. All user-supplied data (especially text data) should be subject to additional verification prior to being transferred to the database.

Does Stored Procedure apply to me?

It may be suitable. Let's summarize their advantages:

•	Improves performance by reducing network traffic
•	Single point of maintenance
•	Abstract business rules to ensure consistency and security
•	Enhances security by minimizing some forms of attacks
•	Support reuse of execution plans

If your environment allows you to take advantage of the benefits provided by stored procedures (as described above), we strongly recommend that you use them. They provide a good tool to improve the way data is processed in the environment. On the other hand, if your environment is vulnerable to factors that compromise these benefits, such as portability, a large number of non-T-SQL-friendly processes, or an unstable database architecture, you may have to consider other approaches.

Another concern is the number of T-SQL professionals in the Organization. Do you have enough T-SQL knowledge? Are you willing to study? Or do you have DBA or appropriate personnel to help you write stored procedures? The more T-SQL knowledge you have, the better the stored procedure, the easier it is to maintain them. For example, a T-SQL is primarily used for set-based operations rather than Row-based operations. Depending on the cursor (because they prompt you for a dataset) will cause performance degradation. If you are not familiar with T-SQL, use this article as a learning opportunity. No matter where you use it, the knowledge described in this article will improve your code.

Therefore, if you believe that stored procedures will add special effects to applications, read this article. We will review some tools that simplify the use of stored procedures and learn some best practices for creating stored procedures.

Notes

If you want to start creating a stored procedure that is used with the application, remember the following tips so that the two can run properly and work well together.

Use set nocount on

By default, the stored procedure returns the number of rows affected by each statement in the process. If you do not need to use this information in an application (not required by most applications), use the set nocount on statement in the stored procedure to terminate this action. Based on the number of statements that affect the row contained in the stored procedure, this will delete one or more round-trips between the client and the server. Although this is not a big problem, it can negatively affect the performance of high-traffic applications.

 
Create procedure test_mystoredproc @ param1 intasset nocount on
 

Do not use SP _ prefix

SP _ prefix is reserved for system stored procedures. The database engine will always search for stored procedures with this prefix in the primary database. This means that when the engine first checks the primary database and then the database where the stored procedure is actually located, it takes a long time to complete the check process. Moreover, if a system stored procedure with the same name exists, your procedure will not be processed at all.

Use as few optional parameters as possible

Before using the optional parameters frequently, consider carefully. Performance is easily affected by the execution of additional work, which is not required for execution based on the set of parameters input for any specified execution. You can solve this problem by combining conditional encoding for each possible parameter, but this is quite time-consuming and increases the chance of errors.

Use output parameters whenever possible

By using the output parameter to return scalar data, You Can slightly increase the speed and save a small amount of processing power. If the application needs to return a single value, try this method instead of externalizing the result set. You can use the output parameter to return the cursor when appropriate, but we will introduce the differences between the cursor processing and set-based processing in subsequent articles.

Return Value

The Return Value of the stored procedure is used to return the processing status information to the called application. In your development team, standardize a set of return values and their meanings, and use these values in a consistent manner. This makes it easier to handle errors in the calling application and provides end users with useful information about the problem.

Use DDL first, and then DML

When you place a DML statement after a DDL statement (in this case, DML references any object modified by DDL), SQL Server recompiles the stored procedure. In this case, to create a plan for DML, SQL Server needs to consider the DDL changes to this object. If you pay attention to all DDL statements starting with a stored procedure, it only needs to be re-compiled once. If DDL and DML statements are used together, the stored procedure is forcibly re-compiled multiple times, which negatively affects the performance.

Always use comments

You may not always maintain this code. However, other people may want to know its purpose in the future. 'Nuff once said this.