SQL Server Stored Procedure Analysis

Advantages of Stored Procedure

Why use stored procedures? The following are the main advantages of Stored Procedure technology:

1. Pre-compiled execution program. SQL Server only needs to compile each stored procedure once, and then the execution plan can be reused. This feature greatly improves the program performance by repeatedly calling the storage program.
2. Reduce the amount of information transmitted between clients and servers. If your work environment bandwidth is limited, the stored procedure technology can certainly meet your needs, because it can shorten the long SQL query to one line.
3. Reuse code and programming effectively. Stored procedures can be used by multiple users or multiple customer programs. This can reduce the time of the program development cycle.
4. Enhance security control. Users can execute stored procedures independently without having to access tables.

　　Structure

The structure of a stored procedure is very similar to that of other programming languages. Stored Procedures accept data in the form of input parameters. These input parameters are used to generate results when executing series statements. The result is returned by using the record set, output parameters, and return code. It sounds complicated. In fact, the storage program is very simple.

　　Instance

Suppose we have the following table named Inventory, and the data in the table needs to be updated in real time. The Warehouse Manager will constantly check the Inventory quantity in the warehouse and the Inventory quantity available for delivery. In the past, warehouse managers in each region performed the following queries:

 

The following is a reference clip: SELECT Product, Quantity FROM Inventory WHERE Warehouse = 'fl'

Such queries make SQL Server very inefficient. Each time the Warehouse Manager executes this query, the database server has to re-compile it and then re-execute it. This query also requires the Warehouse Manager to have SQL knowledge and permission to access table data.

We can simplify the query process by using stored procedures. First, create a process named sp_GetInventory to obtain the inventory level of an existing warehouse. The SQL code for creating the program is as follows:

 

The following is a reference clip: Create procedure sp_GetInventory @ Location varchar (10) AS SELECT Product, Quantity FROM Inventory WHERE Warehouse = @ location

The Warehouse Manager in area A can execute the following command to obtain the inventory level:

 

The following is a reference clip: EXECUTE sp_GetInventory 'fl'

The Warehouse Manager in Area B can use the same stored procedure to access the inventory information in the region.

 

The following is a reference clip: EXECUTE sp_GetInventory 'ny'

Of course, this is just a simple example, but we can see the benefits of stored procedures. The warehouse manager does not have to understand the inherent operating principles of SQL or stored procedures. From the performance perspective, the storage process will undoubtedly greatly improve the work efficiency. SQL Server only needs to create an execution plan once, and then the stored procedure can be reused. You only need to enter appropriate parameters for each execution.

Inventory form:

ID	Product	Warehouse	Quantity
142	Green beans	NY	100
214	Peas	FL	200
825	Corn	NY	140
512	Lima beans	NY	180
491	Tomatoes	FL	80
379	Watermelon	FL	85