SQL Server Stored Procedure Analysis

Source: Internet
Author: User

Advantages of Stored Procedure

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

    1. Pre-compilation and executionProgram. 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. Valid ReuseCodeAnd programming. 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

Structure of the stored procedure and otherProgramming LanguageVery similar. 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: & Lt; TD width = "72" & gt; 100 & lt;/TD & gt; & Lt; TD width = "72" & gt; 200 & lt;/TD & gt; & Lt; TD width = "72" & gt; 140 & lt;/TD & gt; & Lt; TD width = "72" & gt; 180 & lt;/TD & gt;
id product warehouse quantity
142 green beans ny
214 peas FL
825 corn ny
512 lima beans ny
491 tomatoes FL 80
379 watermelon FL 85
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.