A Comment on stored procedures provided for. NET developers)

Preface

The main purpose of this paper is to introduce some key elements when T-SQL statements are encapsulated into the stored procedure according to the needs of the application, so that you can make informed decisions when you apply them in your system. I will introduce some tools and good exercises to developers who want to use them in. net applications.
I am very clear that I am now involved in a struggle similar to that between different religions, a struggle on the transaction logic should be established only on the middle layer or just on the data layer; or is it a debate about writing query code in a program or using stored procedures. Of course, the two methods have their own advantages and disadvantages. What matters is what we need to consider is important to our programs and system environments. So, let's take a look at what a stored procedure is, and consider why we use it to encapsulate T-SQL statements.

Why are stored procedures considered?

Maybe you often use the SqlCommand object in your program and write T-SQL statements to complete data operations, but never considered looking for better places to store these T-SQL statements, instead of mixing them with your data access code. Perhaps as time passes, the functionality of your application continues to increase, resulting in the existence of many complex T-SQL statement code. Stored procedures are a better way to encapsulate these complex statements.
Maybe most people know about stored procedures, but for those who don't know about it, stored procedures are a set of many pre-compiled T-SQL statement sequences, it is stored in the database as an independent code unit. You can input a parameter to send the runtime information to it, or obtain the returned data through the result set or output parameter. When the stored procedure runs for the first time, it is compiled. It generates an execution sequence, essentially a record that records the execution steps of the T-SQL statements specified in the stored procedure for obtaining results. The execution sequence is saved in the cache for future use. This will improve the performance of stored procedures, because when executed again, stored procedures do not need to analyze code to execute tasks, but simply turn to the execution sequence stored in the cache. The cached content remains valid until SQL Server is restarted and during its memory life cycle. Its memory life cycle depends on its minimum memory consumption.

Performance

When querying, the execution sequence in these caches can improve the performance of stored procedures. However, in the last two SQL Server versions, all T-SQL statement batches are stored in the cache as execution sequences, regardless of whether they exist in the stored procedure. Therefore, Performance Improvement Based on this feature is no longer a selling point of stored procedures. Any T-SQL batch processing that uses the static (not frequently changed) syntax is frequently submitted to prevent its execution sequence in the cache from disappearing due to memory lifetime, this can also achieve high performance. Note that "static" is critical because even minor changes (such as comments) do not match the execution sequence in the cache. As a result, the execution sequence cannot be reused.
However, the stored procedure still has its advantages, which can reduce the amount of data transmitted over the network. You only need to send the following command:

 EXECUTE stored_proc_name 

You can easily execute a series of complex operations, rather than those traditional lengthy T-SQL statements. A well-designed storage process can significantly reduce the round-trip communication between the client and the server, and even compress the process to one call.
In addition, the stored procedure on the Remote Call (RPC) server can improve the reusability of the execution sequence, thus improving the performance. When you use a CommandType StoredProcedure SqlCommand object, the stored procedure will be executed through Remote Call (RPC. The Remote Call arranges parameters and calls the stored procedure on the server. This allows the server engine to easily find matching execution sequences and provide updated parameter values. The last thing to consider is whether you fully tap into the power of T-SQL when you prepare an application stored procedure to improve program performance. Consider how you plan to operate the data.

1. Are you doing some dataset-based operations, or some other good operations that the T-SQL already supports? If so, the stored procedure is a good choice, although you can directly write the query statement.

2. Are you performing data row-based operations or complex string operations? If so, you can rethink using T-SQL statements instead of stored procedures. This should be done at least before the latest Yukon release and properly integrated with public language runtime.

Maintainability and commonality Extraction

The second potential benefit worth considering is maintainability. Ideally, your database architecture will not change, and your transaction rules will not change. But in reality, these happen frequently. For example, when you want to add tables x, y, and z to a new promotion, you can simply include these data by modifying the stored procedure, you do not need to go to your program code to find the statements to be modified. Modifying the code in the stored procedure is transparent to the application. You can still get the same sales information, although the internal implementation of the stored procedure has changed. Compared with modifying the existing code, retesting and configuring the program, directly modifying the stored procedure is more time-saving and labor-saving.
In addition, by extracting the same implementation methods and saving them in the stored procedure, the application only needs to use the same operation to access data through these methods. You do not need to maintain the same code distributed in many places, and ensure that your users can obtain consistent information.
Another benefit to maintenance is that saving the T-SQL statement in the stored procedure is also a better way to control the version. You can use scripts that create and modify stored procedures for version control, just like controlling the version of the source code module. By using Visual SourceSafe provided by Microsoft and some other version control tools, you can easily reference and restore to the old version.
Do not misunderstand that using stored procedures to improve maintainability does not avoid the possibility of modifying the data architecture and transaction rules. When these changes are too large, you need to change the input parameters and return values of the stored procedure, and modify your foreground program code, such as adding parameters, updating the call to the GetValue () function, and so on.
Another issue to consider is that the use of stored procedures to encapsulate the transaction logic limits the portability of your application, so that it is bound to SQL Server. If portability is important to your system environment, it is a good choice to encapsulate the transaction logic in an intermediate layer unrelated to the relational database.

Security

The last reason for using the stored procedure is that it can improve the security of the system.
In terms of standardizing user access information, it can provide authorized designated information to users rather than the content of those unauthorized tables. In addition to the ability of the stored procedure to dynamically change data display based on user input, you can think of it as a view in SQL Server (if you are familiar with the view ).
It can also help you escape code security risks. Protect you from an attack called 'SQL injection', which adds operators such as AND OR to your valid input parameters. Stored Procedures also hide the implementation of transaction logic to reduce the risk of application leaks. Because the transaction logic is very important, such information is considered intellectual property rights.
In addition, when using stored procedures, you can use the SqlParameter class provided by ADO.net to provide the correct data type parameters for stored procedures. This also provides us with a method to verify user input parameters, and is also part of a deep defense policy. Note: In a separate query statement, parameters can also narrow the user input range.
However, when using stored procedures to improve security, if you do not have security settings and do not have good coding habits, you will still be exposed to attacks. When creating SQL Server roles and assigning permissions, if you do not pay attention to them, you will be able to access the data they should not see. Do not absolutely think that using stored procedures can completely avoid ''SQL injection'' attacks. Adding data manipulation language (DML) after input parameters can be used for attacks.
Therefore, it is not very secure to use parameters to verify the type of input data, whether in a T-SQL statement or in a stored procedure, the data entered by all users, especially those text data, additional verification should be performed before being transferred to the database.

Is the stored procedure suitable for me?

Oh, maybe. Let's review its advantages:

1. Reduces the amount of data transmitted over the network to improve performance.
2. Convenient single-point Maintenance
3. Extract transaction rules to improve consistency and security
4. Reduce attacks by using input forms to improve security
5. Enhance the reusability of execution sequences

If your system environment allows you to use the advantages of the stored procedure described above, I strongly recommend using it. It provides a good tool to improve data operations in the system. On the other hand, if you require portability and most of the operations that use non-T-SQL, or if you have an unstable database architecture that will offset those advantages above, you should consider other solutions.
Another thing to consider is the degree of T-SQL technology you have mastered. Do you have enough T-SQL knowledge or are you willing to learn? Either you have a database administrator (DBA) or a technical expert who can patiently write the stored procedure for you. The more T-SQL you have, the better your stored procedure will run, and the less effort you have to maintain. For example, Data Set-based T-SQL operations are better than data row-based operations. Therefore, avoid using a cursor, which will degrade your database performance. If you are not very familiar with T-SQL, think it is a good learning opportunity. This knowledge will improve your code, no matter where you write them.
Therefore, for those who believe that stored procedures will certainly bring some benefits to your programs, please read them. We will see some tools that make it easier to work, and learn some examples of how to create it.

Tools provided by Visual Studio. NET

Microsoft Visual Studio. NET provides some tools for you to view and operate SQL Server Stored Procedures (other databases are also supported ). Let's take a quick look at what you expect.

View stored procedures

You can use the server resource manager to view existing stored procedures, to see which parameters it needs, or to see their internal implementation. If you have connected to a Server where the SQL Server database is installed, you can expand the following nodes: server Name> SQL Servers> server instance Name> Northwind> stored procedure, and expand CustOrdersDetail. The manager displays all the parameters required for this stored procedure and the columns it returns. If you view the attributes of these columns, you will notice that these data types are represented as ADO types .. NET Framework documentation provides us with a lightweight manual describing the ing between ADO and. NET data types. Of course, when using parameters in your ADO.net code, you should use the enumerated values of SqlDbType to represent the data types of these parameters. You can refer to the ing between. NET data types and SqlDbType types.
If you double-click the stored procedure you want to view, Visual Studio opens it in the SQL editor and marks different colors on the code. Note that the "create procedure" statement is not displayed in the Code header (in fact, it does exist), but is replaced by the "alter procedure" statement, because the system automatically determines that you want to change the stored PROCEDURE.

Create and modify a stored procedure

Do you have sufficient permissions in the database where you want to create a stored procedure? If you do not have the permission, you can obtain the permission. This is the first step in creating and modifying databases. If you need help, contact your database administrator.
You can create a new stored procedure by right-clicking the Stored Procedure node in the server resource manager and selecting create stored procedure. or ). Then an SQL editor will pop up, which provides the CREATE PROCEDURE statement to help you start writing, and then you can type your Stored PROCEDURE body. Modifying a stored procedure is the same as viewing a stored procedure: first locate the stored procedure in the server resource manager, and then open it.
If you need the system to help you create complex query statements in the stored procedure, right-click the Stored Procedure editing window and Choose Insert SQL from the pop-up menu ]. In addition, you can select a statement block, right-click the block, and select design SQL block from the pop-up menu ]. Both open a query creation window that provides you with a graphical interface to create and modify T-SQL statements. After the design, you can cut and paste it into your stored procedure. Unfortunately, this editor does not provide a keyword-aware system, so be prepared to open SQL Server online help for reference. When saving the stored procedure, the system will warn you that a syntax error has occurred and you need to modify it. Note that errors cannot be saved until they are completely modified. Therefore, you may need to determine the time to complete the error before writing the code. You can first write a piece of code and then run the check in the SQL Server Query analyzer. This is repeated, but this is the content of another article.
Once you have compiled your stored procedure, you can right-click it and choose run stored procedure to test your stored procedure.

Getting started

If you start to create a stored procedure for your application, here are some tips, please remember them, this will make your stored procedure run better and work closely with other programs.

Use the set nocount on statement

In the default settings, the stored procedure returns the number of rows affected by SQL statement execution. If you do not need this information in your program (not required by most programs), you can use the set nocount on statement to disable this function. Based on the number of statements that affect data rows in your stored procedure, this can reduce the number of back-trips between the server and the client. This may not be a big problem, but it will significantly reduce performance in a program with a high data transmission load.

create procedure test_MyStoredProc @param1 int

as 

set nocount on

Do not use the SP _ prefix. The SP _ prefix is reserved for system-level stored procedures. The database engine always looks for a stored procedure with a prefix of SP _ In the master database. This means that the stored procedure to be executed will be searched in the master database before it takes some time. Worse, if a system-level stored procedure has the same name as your stored procedure, your stored procedure will never be executed.

Use as few optional parameters as possible

When using a large number of optional parameters, consider clearly. If you enter a set of unnecessary parameters when executing a stored procedure, this extra work will affect the performance of the stored procedure. This problem occurs when you use conditional control encoding to obtain different parameter combinations, which wastes your time and increases the chance of errors.

Use output parameters when needed

You can use output parameters to return the required data, which can speed up your development and reduce the processing trouble. When your program needs to return a single value, you can use this method to replace the traditional method of instantiating a result set. You can also return a cursor through the output parameter when appropriate. We will discuss the cursor and dataset-based operations in future articles.

Returns a value.

Return the status information of the stored procedure to the application that calls the stored procedure. Within your development team, standardize a series of return values and their meanings and use them in a unified manner. This not only makes it easier to handle errors in calls, but also provides useful information for your end users to solve their problems.

Write DDL first, then DML

When the data control language (DML) is executed before the Data Definition Language (DDL) in the stored procedure, SQL Server re-compiles the stored procedure, the data manipulation language references objects changed by the Data Definition Language. This is because SQL Server needs to count the object changes caused by the Data Definition Language to create an execution plan for the data manipulation language. If you plan the data definition language at the beginning, you only need to recompile it once. If you write the two together, the stored procedure will be forced to recompile multiple times, which will greatly affect the performance of the program.

Always write comments

You may not maintain the Code any more. But it can be said that in the future, other people will maintain the code, and they need to know the functions of the Code.

Summary

Whether stored procedures are useful to you, I hope you have some feelings. It is a free tool provided by SQL Server, so when it helps your application environment and maintenance, you should indeed use it. The above information helps you start using stored procedures. SQL Server online reference manual, Visual Studio documentation, and of course the in-depth information on MSDN can push you forward.