ASP call SQL Server Stored Procedure learning tutorial

Source: Internet
Author: User
I. First, let me talk about the advantages and disadvantages of calling a stored procedure: 1. SQL statements have been pre-compiled, so the execution efficiency and performance have greatly increased. 2. Parameters and output parameters are acceptable, and one or more result sets are returned and returned. The error cause can be returned to the program. 3. reduce network traffic. For example, when the insert record function is executed, as long as the data is transmitted and stored

I. First, let me talk about the advantages and disadvantages of calling a stored procedure: 1. SQL statements have been pre-compiled, so the execution efficiency and performance have greatly increased. 2. Parameters and output parameters are acceptable, and one or more result sets are returned and returned. The error cause can be returned to the program. 3. reduce network traffic. For example, when the insert record function is executed, as long as the data is transmitted and stored

I. Let me first talk about the advantages and disadvantages of calling a stored procedure:

1. SQL statements have been pre-compiled, so the execution efficiency and performance are greatly increased.

2. Parameters and output parameters are acceptable. One or more result sets and return values are returned. The error cause can be returned to the program.

3. reduce network traffic. For example, when you perform the insert record function, you only need to transmit the stored procedure name, parameters, and values of these parameters. Avoid writing a large string of SQL statements.

We will find how useful this is when the network speed is slow, that is, reducing the pressure on the client and simplifying a series of complex statements.

4. Reduce injection attacks. Stored Procedures can contain program streams, logic, and queries to databases. Data logic can be encapsulated and hidden by entities.

5. A stored procedure can execute a series of SQL statements in a single stored procedure, and other stored procedures can be nested in the stored procedure.

6. It is conducive to the reuse of SQL statements.

7. better adaptability: Because stored procedures access databases through stored procedures, therefore, database developers can make any changes to the database without modifying the stored procedure interface,

These changes do not affect the application.

8. Protocol work: the coding work of applications and databases can be performed independently without mutual suppression..

It is appropriate to use a stored procedure when a business processes multiple tables at the same time.

  1. Using Stored Procedures improves performance in general, because the database optimizes the data access plan of stored procedures and applies the cache to facilitate future queries;
  2. Stored procedures are independently protected in databases. The client can obtain the permission to execute the stored procedure without setting other access permissions for the underlying table;
  3. Stored procedures make maintenance easier, because it is easier to modify a stored procedure than to modify an SQL statement in a published component;
  4. The stored procedure adds an additional abstraction layer to the underlying data format. The Implementation Details of the stored procedure on the client using the stored procedure and the isolation of the underlying data format are independent;
  5. Stored Procedures can alleviate network bandwidth because you can execute SQL statements in batches instead of sending over-load requests from the client.

Complex data processing uses stored procedures, such as some report processing.

Multi-condition multi-table joint query and paging processing are also suitable for using stored procedures.

Appropriate use of stored procedures can improve the performance of SQL queries, so as to improve our work efficiency. This article introduces you so much that I hope this introduction will be helpful to you!

Disadvantages of stored procedures:

1. Poor portability. I have been using SQL server for development, but if I want to sell my own things, I find myself selling things for ms. If you want to replace it with mysql, it is really troublesome to transplant it.

2. To use the stored procedure call class, you need to perform two call operations. One is to obtain the parameter information of the process from SQL server and create parameters. The second is to call this process. Once more.

However, this disadvantage can be solved after the project is developed and the process parameters are completely determined. All the process parameter information can be imported into an xml file to improve the performance.

II. The following describes how to call and create a stored procedure:

Step 1: Create a stored procedure

--- Create a stored procedure template
Create PROCEDURE [stored PROCEDURE name]
@ Param nvarchar (4) --- stored procedure parameters
AS
Set nocount on
Declare @ SQL nvarchar (1000)
Set @ SQL = 'SQL statement to be executed by the Stored Procedure'
Exec sp_executesql @ SQL ----- execute an SQL statement

First, I will create a stored procedure for all the contents of a table:

------- Create
Create procedure [KS_ShowContent]
@ TableName nvarchar (50)
AS
Set nocount on
Declare @ SQL nvarchar (1000)
Set @ SQL = 'select * from' + @ TableName

Exec sp_executesql @ SQL

Step 2: Call the Stored Procedure

There are two ways to call a stored procedure:

Method 1:
Set rs = Conn. Execute ("Stored Procedure name Parameters ")

For example, call the [KS_ShowContent] stored procedure:

Set rs = Conn. Execute ("KS_ShowContent UserTabl") --- UserTabl is a table name.
Do while not rs. eof

Id = rs ("id ")

Rs. movenext

Loop

Rs. close: set rs = nothing

Method 2:

Dim Cmd: Set Cmd = Server. CreateObject ("ADODB. Command ")
Set Cmd. ActiveConnection = conn
Cmd. CommandText = "KS_TSql" 'stored procedure name
Cmd. CommandType = 4
CMD. Prepared = true
Cmd. Parameters. Append cmd. CreateParameter ("@ SQL", 8000, SQLStr) 'sets Parameters and sets parameter values
Set Rs = Cmd. Execute
Set Cmd = nothing
Do while not rs. eof

Id = rs ("id ")

Rs. movenext

Loop

Rs. close: set rs = nothing

---------- Comment
CreateParameter (Name, Type, Direction, Size, Value)
· Name (optional) is a string that represents the Parameter object Name.
· Type (optional) long integer value, which specifies the Data Type of the Parameter object. BigInt: 20; Binary: 128; boolean: 11; Char: 129; BtimeStamp: 135; Empty: 0; Integer: 3;
SmallInt: 2; TinyInt: 16; VarChar: 200; advarchar 202 unencoded string variable value
· Direction (optional) long integer value, which specifies the Parameter object type. 0: Indicates pollution determination; 1: Indicates input parameters; 2: Indicates input parameters; 3: Indicates input or output parameters; 4: Indicates return values.
· Size (optional) long integer value. It specifies the maximum length of the parameter value (in characters or bytes ).
· Value (optional) specifies the Parameter object Value.
CommandType has different values, and CommandText has different meanings:
-1 indicates that the CommandText parameter type cannot be confirmed.
1 indicates that the CommandText parameter is a common command
2. The CommandText parameter is an existing table name.
4. The CommandText parameter is an existing stored procedure.

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.