Application of T-SQL Stored Procedure

Summary
This article introduces the basic concepts of stored procedures in SQL Server and how to use stored procedures in Visual Studio. using Stored Procedures in the integrated development environment of. Net to improve the efficiency of application execution, and describes how to optimize the stored procedure to further improve the efficiency of data access, and the relationship between stored procedures and data security.
Off
Key word storage process C # optimizes Data Security

1 Introduction


When Ms sqlserver creates an application, transaction-SQL
Is a major programming language. If you use the SQL language for programming, there are two methods. First, store the transaction-SQL program locally and create an application
Server sends commands to process the results. Second, some programs written using transaction-SQL can be stored in SQL Server as stored procedures.
And create an application to call the stored procedure, return the results, and then process the data results.
This article introduces the second method, that is, using stored procedures to access the database.

2 Introduction to stored procedures
2.1 concepts and advantages of Stored Procedures


Stored Procedure is a set of SQL statements that complete specific functions.
Statement set, which is stored in the database after compilation. The stored procedure can return the result set to the caller by receiving parameters. The format of the result set is determined by the caller. The caller can return the status value to indicate whether the call is successful or
Failure: includes database operation statements, and can call another stored procedure in one stored procedure.
Stored Procedures have the following advantages:
① Stored procedures allow standard component programming
After a stored procedure is created, it can be called multiple times in the program without having to rewrite the stored procedure. Database professionals can modify stored procedures at any time, but it has no impact on the application source code (because the application source code only contains the calling statements of stored procedures ), this greatly improves the portability of the program.
② The stored procedure can achieve fast execution speed

If an operation contains a large number of SQL statements
If the code is executed multiple times, the storage process is much faster than the batch processing. Because the stored procedure is pre-compiled, when a stored procedure is run for the first time, the query optimizer analyzes, optimizes it, and
Shows the execution plan that is finally stored in the system table. The SQL statements for batch processing must be compiled and optimized each time they are run, so the speed is relatively slow.
③ Stored procedures can reduce network traffic
For the same operation on database objects (such as queries and modifications), if the SQL statements involved in this operation are organized into a stored procedure, when the stored procedure is called on the client's computer, only the calling statement is sent on the network. Otherwise, multiple SQL statements are sent, greatly increasing network traffic and reducing network load.
④ Stored procedures can be fully utilized as a security mechanism

The system administrator restricts the permissions to execute a stored procedure to avoid unauthorized users accessing data and ensure data security. Stored Procedure hiding
The database mode and programming details are provided. When you use the client SQL code to query a database, you need to know the details of the queried tables and columns, this will display the database mode in front of the customer connection and customer, and store
The process does not allow the client to understand the details. The only information that the client or connection has is the name of the stored procedure to be called. From this perspective, the stored procedure provides a data security layer.
2.2 stored procedure type

Sqlserver supports the following five types of stored procedures:
System stored procedures: These stored procedures are built into SQL Server and cannot be modified at will. They provide information about database modes, object names, constraints, data types, and licenses.
Local stored procedures: These stored procedures are compiled by database administrators or SQL server developers and are the focus of this article.
Temporary stored procedures: These stored procedures are compiled by database administrators and SQL server developers, but only exist during connection.
Remote stored procedures: These stored procedures exist on remote servers and can be referenced by an initial server. Remote stored procedures are used in distributed applications.
Extended stored procedures: These stored procedures are functionally similar to local stored procedures, but they can reference functions outside sqlserver.
2.3 SQL Server Processes stored procedures


The processing of stored procedures is divided into two stages. In the first stage, after a stored procedure is created, it is first parsed by the SQL Server database engine. Two things will happen in the future. First, SQL
Server saves the definition, name, and code of the process to the database.
The server also delivers the code to the query optimizer to determine the optimal execution plan of the Code. Second, the code is compiled and placed in the process cache, only when the customer connection shows that the call re-compilation or execution plan is not
When stored in the cache, the execution plan in the cache is refreshed. 1.

 

In the second stage, when the stored procedure name is referenced in the Code, the query plan is retrieved. The process code is executed in the context of each connection that calls it, any result set or return value is returned to every connection. 2.

 

2.4 handle errors in Stored Procedures


If an error occurs during execution of the stored procedure, sqlserver returns the error code and error message. SQL Server
Nearly 3800 predefined error codes are stored in the sysmessages table of the master database. Each error code has a precise level. The error definition level ranges from 0
25. More than 20 errors indicate a major error, which means that the storage process is terminated immediately and all connections must be reinitialized. Non-critical errors only prevent code that is currently running with errors,
And continue to execute the remaining code. All error codes have predefined error messages. You can obtain the error information based on the error code and return it to the calling program of the stored procedure, which facilitates the program writer to handle the error.

3. Call the stored procedure in vs.net

The following uses the northwind database as an example to describe how to call the Stored Procedure (C #) in the integrated development environment of visual studio.net #).
3.1 create a stored procedure

You can use the create Stored Procedure wizard in sqlserver Enterprise Manager or directly write SQL statements to create a stored procedure based on the requirements of the client.
List.1 Stored Procedure List
[1] Create procedure count_customerid
[2] @ customerid nchar (5 ),
[3] @ num int output
[4]
[5] Select @ num = (select count (*) from orders where customerid = @ customerid)
[6] Return
[7] Go

The preceding SQL statement creates a stored procedure named count_customerid, which has an input parameter (Parameter Name: customerid; Class
Type: nchar (5); meaning: ID value of the product to be identified) and an output parameter (Parameter Name: num; Type: int; meaning: order quantity of a product ). Functions of the stored procedure:
Calculate the order quantity of the product based on the product ID value passed in when the client calls the stored procedure, and return the result as an output parameter to the client caller.
NOTE: For the syntax for creating a stored procedure, see SQL Server books online.
3.2 design applications

After the database and stored procedures are designed, you can start designing applications. The first thing to do is to connect to the database. You can use the sqlconnection object (named empty
System. Data. sqlclient), oledbconnection object (namespace: system. Data. oledb ),
Odbcconnection
Object (namespace: system. Data. ODBC) and other methods to connect to the database. This topic describes how to use a sqlconnection object to connect to a database.
List.2 call the Stored Procedure
// Add a namespace
Using system. Data. sqlclient;
// Use the sqlconnection object to connect to the database
String connstr;
Connstr = "Data Source = Local; initial catalog = northwind; user id = user; Password = pw ";
Sqlconnection conn = new sqlconnection (connstr );
// Use sqlcommand to call the Stored Procedure
Sqlcommand cmd = new sqlcommand (custordersorders, Conn); // create a sqlcommand object
Conn. commandtype = commandtype. storedprocedure;
Sqlparameter custid = new sqlparameter ("@ customerid", sqldbtype. Char, 5 );
Custid. value = "alfki"; // assign a value to the input parameter
Cmd. Parameters. Add (custid );
Sqlparameter COUNT = new sqlparameter ("@ num", sqldbtype.int, 4 );
Cmd. Parameters. Add (count );
Count. Direction = parameterdirection. output; // specify the parameter as the output parameter.
// Start executing the Stored Procedure
Conn. open (); // open the database connection
Cmd. executenonquery (); // execute the query
Conn. Close ();
// The sum value is the order quantity of the specified item returned by the stored procedure.
Int sum = convert. toint32 (CMD. Parameters ["@ total"]. value. tostring ());

4. Other features of Stored Procedures
4.1 extended stored procedure

When writing a data access program, you sometimes need to implement some functions that cannot be implemented by sqlserver itself, such as accessing local resources. In this case, you can use the extended stored procedure. The extended storage process is
In the form of a dynamic link library, use the system stored in the master database in the Stored Procedure sp_addextendedproc to write the dynamic link library
The function declaration in (. dll file) is an extended stored procedure. After the declaration, you can call this function to implement some of the functions to be implemented.
The specific syntax is omitted.
4.2 call COM components

Com, a component object model, is an object model that uses components as release units. This model allows various software components to interact in a unified manner, it does not depend on any specific language or operating system.
Com makes more and more programming developers adopt this programming method. The stored procedure in SQL Server also supports the COM programming method.
Compile the COM component as needed. After the component is released, instantiate the COM Object using the system stored procedure sp_oacreate in the master database, and then use the function in the COM object.
The specific syntax is omitted.

5. Optimize the Stored Procedure

If an application uses a large number of stored procedures and does not have certain writing specifications, it will affect system maintenance and make it hard to understand the Stored Procedure logic, in addition, if the database has a large amount of data or
If the performance requirement of the process is very high, you may encounter optimization problems. Otherwise, the speed may be slow. An Optimized stored procedure is hundreds of times more efficient than a poorly performing stored procedure.
Note the following points when writing a stored procedure:
① Avoid large transaction operations and improve the system concurrency;
② Avoid repeatedly accessing the same or several tables, especially tables with large data volumes. You can consider extracting data to the temporary table based on the conditions and then connecting to the temporary table to access the temporary table;
③ Avoid using a cursor whenever possible because the cursor is inefficient. If the cursor operation contains more than 10 thousand rows of data, it should be rewritten. If the cursor is used, try to avoid table join operations in the cursor loop;
④ Pay attention to the writing of where statements. The order of statements must be taken into account. The order of condition clauses should be determined based on the index order and range, and the field order should be consistent with the index order as much as possible, the range ranges from large to small;
⑤ Do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the WHERE clause; otherwise, the system may not be able to correctly use the index;
⑥ Use exists instead of select count (1) to determine whether a record exists. The count function is used only when all the rows in the statistical table are used, and count (1) is more efficient than count.
7. Try to use "> =" instead of "> ";
Except pay attention to some replacement between the or clause and the union clause;
⑨ Pay attention to the data types connected between tables to avoid the connection between different types of data;
In addition, you must use indexes and operate temporary tables correctly and efficiently. Reasonable algorithm usage is also an effective means to optimize the storage process and improve the program execution efficiency.

6. stored procedures and data security

From the perspective of users' access to sensitive data, you can control access to specific data by granting users access permissions to stored procedures (rather than basic tables, protects sensitive data.
Stored procedures can also help solve code security issues. It can 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.
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.

When using stored procedures to enhance data security, you must note that poor security or encoding practices are still under attack. For SQL Server
If you do not pay attention to role creation and allocation, data that cannot be seen will be accessed. At the same time, stored procedures cannot prevent all SQL insert code attacks (for example, the data operation language (DML)
Attached to input parameters ).
In addition, whether the SQL statement is in the code or stored procedure, it is not foolproof to use parameters for data type verification. All user-supplied data (especially text data) should be subject to additional verification prior to being transferred to the database.

7 conclusion

This article briefly introduces the concept, features, and application methods of stored procedures, so that you can have a preliminary understanding of stored procedures. The wide application of a stored procedure and its data access mechanism make the stored procedure a three-tier knot currently advocated
An essential element of the data access layer in the system development model. We hope to provide some reference and guidance for developers who use databases to develop applications.