SQL Server stored Procedure parsing _mssql

benefits of Stored procedure functionality

Why use stored procedures? The following are some of the major benefits of stored procedure technology:

1. Precompile the execution program. SQL Server only needs to compile each stored procedure once, and then you can reuse the execution plan. This feature greatly improves the performance of the program by repeatedly invoking the stored program.
2. Reduce the amount of information transferred between clients/servers. If your working environment is limited in bandwidth, then the stored-process technology will certainly satisfy you, because it can shorten the long SQL queries that need to be transferred into one line.
3. Effectively reuse code and programming. Stored procedures can be used by multiple users or can be used with multiple client programs. This can reduce the time of the program development cycle.
4. Enhance security control. You can allow users to execute stored procedures separately, without giving them permission to access the table.

　　Structure

The structure of stored procedures is very similar to other programming languages. Stored procedures accept data in the form of input parameters. These input parameters are used when executing the series statement and generate the results. The results are returned by using recordsets, output parameters, and return codes. It sounds complicated, and it's actually very simple to store the program.

　　Instance

Suppose we have the following table named Inventory, the data in the form needs to be updated in real time, and the warehouse manager keeps checking the quantity of the stock in the warehouse and the quantity of the goods available for shipment. In the past, warehouse managers in every area would do the following:

The following is a reference fragment: SELECT Product, Quantity From Inventory WHERE Warehouse = ' FL '

Such queries make SQL Server performance very inefficient. Each time the warehouse manager executes the query, the database server has to recompile it and restart execution. Such queries also require the warehouse manager to have knowledge of SQL and have permission to access tabular data.

We can simplify this query process by using stored procedures. First, create a process called sp_getinventory, which is able to capture the level of storage in an existing warehouse. The following is the SQL code that created the program:

The following is a reference fragment: 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 order to obtain the deposit level:

The following is a reference fragment: EXECUTE sp_getinventory ' FL '

Warehouse Managers in Area B can use the same stored procedures to access the storage information in the area.

The following is a reference fragment: EXECUTE sp_getinventory ' NY '

Of course, this is just a simple example, but you can see the benefits of the stored procedure. The warehouse manager does not necessarily have to understand how the SQL or stored procedures work internally. From the performance point of view, the stored procedures undoubtedly greatly improve the efficiency of the work. SQL Server only needs to create an execution plan once, and then you can reuse the stored procedure, simply by entering the appropriate parameters each time you execute it.

Goods deposit 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