SQL Server Stored Procedures

Last semester in the company's internship in the company's national map of the project, found that the front-end of the rapid search, statistical analysis and so on is actually called the database server side of the stored procedures or views, before the school at the time of the stored procedure Mengmengdongdong, do not know how to use the specific how to achieve the purpose of optimization, Recently viewed a lot of data, think the stored procedure is really useful.

1. Improve performance

The execution of the SQL statement needs to be compiled first, the stored procedure is a set of SQL statements in order to complete a specific function, it can be compiled once, the next execution is no longer compiled, so that the front-end calls can improve the efficiency of the operation, and the more the number of calls is basically the faster.

2. Security

Stored procedures on the database server side, in front of only need to call the name and parameters on the line, to prevent SQL injection; In a graph project, the company specifically developed a datacenter is the Data Transformation Center, all the query and statistical analysis are written to the service, so that only the front end needs to invoke services, Configuring the name and parameters of the stored procedure that needs to be called returns the desired effect for the user.

3. Reduce network traffic

You only need to pass in the name and parameter values of the stored procedure, and do not transfer the full SQL to save traffic.

4. Encapsulation

A method that can be used to manipulate a database object, and the user only needs to know its input and output parameters and understand its purpose.

5. Importance

For complex logic, the application of stored procedures that have been tested is not prone to errors.

SQL Server Stored Procedures