Detailed introduction of ASP program and SQL stored procedure

Definition is always abstract. The stored process is actually a set of SQL statements that can perform certain operations, but these statements are stored in the database (Here we only talk about SQL Server ). If we create a stored process and call the stored process in ASP, we can avoid comparing SQL statements with ASPCode. There are at least three advantages:
First, greatly improve efficiency. The execution speed of the stored process itself is very fast, and calling the stored process can greatly reduce the number of interactions with the database.
Second, improve security. If SQL statements are mixed in ASP code, once the code is out of password, it also means that the database structure is out of password.
Third, it is conducive to the reuse of SQL statements.

In ASP, the stored process is generally called through the command object. Based on different situations, this article also introduces other call methods. For convenience, the following simple classification is made based on the input and output of the storage process:
1. Only the stored process of a single record set is returned.
Suppose there are the following stored processes (the purpose of this article is not to describe the T-SQL syntax, so the stored process only gives code, not to mention ):

/* SP1 */
Create procedure DBO. getuserlist
As
Set nocount on
Begin
Select * From DBO. [userinfo]
End
Go

The preceding Stored Procedure Retrieves all records in the userinfo table and returns a record set. The ASP code for calling the stored process through the command object is as follows:

'** Call the stored process through the command object **
Dim mycomm, myrst
Set mycomm = server. Createobject ("ADODB. Command ")
Mycomm. activeconnection = myconstr 'myconstr is the database connection string
Mycomm. commandtext = "getuserlist" 'specifies the name of the stored process.
Mycomm. commandtype = 4 'indicates that this is a stored Process
Mycomm. Prepared = true 'requires that the SQL command be compiled first
Set myrst = mycomm. Execute
Set mycomm = nothing

The record set obtained by the storage process is assigned to myrst. Next, you can operate on myrst.
In the preceding code, the commandtype attribute indicates the request type. The values and descriptions are as follows:
-1 indicates that the commandtext parameter type cannot be determined
1 indicates that commandtext is a common command type
2 indicates that the commandtext parameter is a table name
4. The commandtext parameter is the name of a stored process.

You can also call the stored process through the connection object or recordset object. The methods are as follows:

'** Call the stored process through the connection object **
Dim myconn, myrst
Set myconn = server. Createobject ("ADODB. Connection ")
Myconn. Open myconstr 'myconstr is the database connection string
Set myrst = myconn. Execute ("getuserlist",) 'the last parameter is the same as commandtype
Set myconn = nothing

'** Call the stored process through the recordset object **
Dim myrst
Set myrst = server. Createobject ("ADODB. recordset ")
Myrst. Open "getuserlist", myconstr, 0, 1, 4
'Myconstr is the database connection string. The last parameter is of the same meaning as commandtype.

2. storage process without input/output
See the following stored procedures:

/* SP2 */
Create procedure DBO. deluserall
As
Set nocount on
Begin
Delete from DBO. [userinfo]
End
Go

The stored process deletes all records in the userinfo table without any input or output. The Calling method is basically the same as described above, but the record set does not need to be obtained:

'** Call the stored process through the command object **
Dim mycomm
Set mycomm = server. Createobject ("ADODB. Command ")
Mycomm. activeconnection = myconstr 'myconstr is the database connection string
Mycomm. commandtext = "deluserall" 'specifies the name of the stored process.
Mycomm. commandtype = 4 'indicates that this is a stored Process
Mycomm. Prepared = true 'requires that the SQL command be compiled first
Mycomm. Execute 'you do not need to retrieve the record set here
Set mycomm = nothing

Of course, you can also call this type of storage process through the connection object or recordset object. However, the recordset object is created to obtain the record set. If the record set is not returned, use the command object.

3. Stored processes with returned values
When performing SP2-like operations, SQL Server's powerful transaction processing functions should be fully utilized to maintain data consistency. In addition, we may need the storage process to return the execution status. For this reason, modify SP2 as follows:

/* SP3 */
Create procedure DBO. deluserall
As
Set nocount on
Begin
Begin transaction
Delete from DBO. [userinfo]
If @ error = 0
Begin
Commit transaction
Return 1
End
Else
Begin
Rollback transaction
Return 0
End
Return
End
Go

The above stored process returns 1 when the delete operation is executed successfully. Otherwise, 0 is returned and rollback is performed. To obtain the return value in ASP, you must use the parameters set to declare the parameter:

'** call a stored process with a returned value and obtain the returned value **
dim mycomm, mypara
set mycomm = server. createobject ("ADODB. command ")
mycomm. activeconnection = myconstr 'myconstr is the database connection string
mycomm. commandtext = "deluserall" 'specifies the name of the stored Process
mycomm. commandtype = 4' indicates that this is a stored Process
mycomm. prepared = true' requires that the SQL command be compiled first
'declare the return value
set mypara = mycomm. createparameter ("return", 2, 4)
mycomm. parameters. append mypara
mycomm. execute
'obtain the returned value
dim retvalue
retvalue = mycomm (0)' or retvalue = mycomm. parameters (0)
set mycomm = nothing

In mycomm. createparameter ("return", 2, 4), the meanings of parameters are as follows:
The first parameter ("Reture") is the parameter name. The parameter name can be set arbitrarily, but it should be the same as the parameter name declared in the stored process. Here is the return value. I used to set it to "Reture ";
The second parameter (2) indicates the Data Type of the parameter. For details about the type code, see the ADO reference. The following describes the common type code:
Adbigint: 20;
Adbinary: 128;
Adboolean: 11;
Adchar: 129;
Addbtimestamp: 135;
Adempty: 0;
Adinteger: 3;
Adsmallint: 2;
Adtinyint: 16;
Advarchar: 200;
For return values, only integer values can be taken and-1 to-99 are reserved values;
The third parameter (4) indicates the nature of the parameter. Here 4 indicates that this is a return value. The value of this parameter is described as follows:
0: The type cannot be determined; 1: input parameter; 2: input parameter; 3: input or output parameter; 4: Return Value

The above ASP code should be the complete code, that is, the most complex code. In fact

Set mypara = mycomm. createparameter ("return", 2, 4)
Mycomm. Parameters. append mypara

Can be simplified

Mycomm. Parameters. append mycomm. createparameter ("return", 2, 4)

It can even be simplified and will be explained later.
For a stored process with parameters, you can only call it using the command object (It is also said that it can be called through the connection object or recordset object, but I have not tried it ).
4. Stored processes with Input and Output Parameters
The returned value is actually a special output parameter. In most cases, we use a stored process with both input and output parameters. For example, if we want to obtain the username of an ID in the user information table, there is an input parameter ---- user ID, and an output parameter ---- user name. The storage process that implements this function is as follows:

/* SP4 */
Create procedure DBO. GetUserName
@ Userid int,
@ Username varchar (40) Output
As
Set nocount on
Begin
If @ userid is null return
Select @ username = Username
From DBO. [userinfo]
Where userid = @ userid
Return
End
Go

The ASP code for calling the stored process is as follows:

'** Call a stored process with input and output parameters **
Dim mycomm, userid, username
Userid = 1
Set mycomm = server. Createobject ("ADODB. Command ")
Mycomm. activeconnection = myconstr 'myconstr is the database connection string
Mycomm. commandtext = "GetUserName" 'specifies the name of the stored process.
Mycomm. commandtype = 4 'indicates that this is a stored Process
Mycomm. Prepared = true 'requires that the SQL command be compiled first
'Declare Parameters
Mycomm. Parameters. append mycomm. createparameter ("@ userid", 3, 1, 4, userid)
Mycomm. Parameters. append mycomm. createparameter ("@ username", 40)
Mycomm. Execute
'Get output parameter
Username = mycomm (1)
Set mycomm = nothing

In the above code, we can see that, unlike the declared return value, five parameters are required for declaring input parameters, and four parameters are required for declaring output parameters. When declaring input parameters, the five parameters are: Parameter Name, parameter data type, parameter type, data length, and parameter value. When declaring output parameters, there is no last parameter: parameter value.
Note: When declaring parameters, the order must be the same as that defined in the stored process, the data type and length of each parameter must be the same as that defined in the stored process.
If a stored process has multiple parameters, ASP code is cumbersome. You can use the with command to simplify the Code:

'** Call a stored process with input and output parameters (simplified code )**
Dim mycomm, userid, username
Userid = 1
Set mycomm = server. Createobject ("ADODB. Command ")
With mycomm
. Activeconnection = myconstr 'myconstr is the database connection string
. Commandtext = "GetUserName" 'specifies the name of the stored Process
. Commandtype = 4' indicates that this is a stored Process
. Prepared = true' requires that the SQL command be compiled first
. Parameters. append. createparameter ("@ userid", 3, 1, 4, userid)
. Parameters. append. createparameter ("@ username", 40)
. Execute
End
Username = mycomm (1)
Set mycomm = nothing

If we want to get the username with IDs of 1 to 10, 10 users, do we need to create 10 command objects? No. If you need to call the same stored process multiple times, you only need to change the input parameters to get different outputs:

'** Call the same stored process multiple times **
Dim mycomm, userid, username
Username = ""
Set mycomm = server. Createobject ("ADODB. Command ")
For userid = 1 to 10
With mycomm
. Activeconnection = myconstr 'myconstr is the database connection string
. Commandtext = "GetUserName" 'specifies the name of the stored Process
. Commandtype = 4' indicates that this is a stored Process
. Prepared = true' requires that the SQL command be compiled first
If userid = 1 then
. Parameters. append. createparameter ("@ userid", 3, 1, 4, userid)
. Parameters. append. createparameter ("@ username", 40)
. Execute
Else
'Assign a value to the input parameter again (in this case, the input parameter and output parameter values that do not change do not have to be declared again)
. Parameters ("@ userid") = userid
. Execute
End if
End
Username = username + mycomm (1) + "," 'Maybe you like to use Array Storage
Next
Set mycomm = nothing

The code above shows that when you repeatedly call the same stored process, you only need to assign a value to the input parameter whose value has changed. This method has multiple input and output parameters, when only one input parameter value changes during each call, the amount of code can be greatly reduced.

5. Stored processes with return values, input parameters, and output parameters
As mentioned above, when calling a stored process, the order of declared parameters must be the same as that defined in the stored process. Note: If a stored process has both return values and input and output parameters, the return values must be declared first.
To demonstrate the call method in this case, we can improve the above example. Or get the username of the user with ID 1, but it is possible that this user does not have (this user has been deleted, and userid is a self-increasing field ). The stored process returns different values based on whether the user has them or not. The stored process and ASP Code are as follows:

/* SP5 */
Create procedure DBO. GetUserName
-- To enhance the impression of "sequence", the following two parameters are defined in reverse order.
@ Username varchar (40) output,
@ Userid int
As
Set nocount on
Begin
If @ userid is null return
Select @ username = Username
From DBO. [userinfo]
Where userid = @ userid
If @ rowcount> 0
Return 1
Else
Return 0
Return
End
Go

'** call a stored process with both return values, input parameters, and output parameters **
dim mycomm, userid, username
userid = 1
set mycomm = server. createobject ("ADODB. command ")
with mycomm
. activeconnection = myconstr 'myconstr is the database connection string
. commandtext = "GetUserName" 'specifies the name of the stored Process
. commandtype = 4' indicates that this is a stored Process
. prepared = true' requires that the SQL command be compiled first
'Return value should be declared first
. parameters. append. createparameter ("return", 2, 4)
'the Declaration Order of the following two parameters is also reversed.
. parameters. append. createparameter ("@ username", 40)
. parameters. append. createparameter ("@ userid", 3,1, 4, userid)
. execute
end with
If mycomm (0) = 1 then
username = mycomm (1)
else
username = "this user does not have"
end if
set mycomm = nothing
6. stored processes that return both parameters and Record Sets
sometimes, we need the stored process to return both parameters and record sets. For example, when using the stored process paging function, parameters such as the record set and total data volume must be returned at the same time. Here is a stored process for paging processing:

/* SP6 */
Create procedure DBO. getuserlist
@ Ipagecount int output, -- total number of pages
@ Ipage int, -- current page number
@ Ipagesize int -- number of records per page
As
Set nocount on
Begin
-- Create a temporary table
Create Table # T (ID int identity, -- auto-increment Field
Userid int,
Username varchar (40 ))
-- Write data to the temporary table
Insert into # T
Select userid, username from DBO. [userinfo]
Order by userid

-- Retrieve the total number of records
Declare @ irecordcount int
Set @ irecordcount = @ rowcount

-- Determine the total number of pages
If @ irecordcount % @ ipagesize = 0
Set @ ipagecount = ceiling (@ irecordcount/@ ipagesize)
Else
Set @ ipagecount = ceiling (@ irecordcount/@ ipagesize) + 1

-- If the requested page number is greater than the total page number, the last page is displayed.
If @ ipage> @ ipagecount
Select @ ipage = @ ipagecount

-- Determine the start and end records of the current page
Declare @ istart int -- start record
Declare @ iend int -- end record
Select @ istart = (@ ipage-1) * @ ipagesize
Select @ iend = @ istart + @ ipagesize + 1

-- Retrieve the current page record
Select * from # t where ID> @ istart and ID <@ iend

-- Delete a temporary table
Drop table # T

-- Total number of returned records
Return @ irecordcount
End
Go

In the preceding stored procedure, enter the current page number and the number of records on each page to return the record set, total page number, and total number of records on the current page. To be more typical, the total number of records is returned as a return value. The following is the ASP code that calls the stored process (the specific paging operation is omitted ):

'** Call the paging storage process **
Dim pagenow, pagesize, pagecount, recordcount
Dim mycomm, myrst
Pagenow = request ("PN ")
'Udf is used to verify the natural number
If checknar (pagenow) = false then pagenow = 1
Pagesize = 20
Set mycomm = server. Createobject ("ADODB. Command ")
With mycomm
. Activeconnection = myconstr 'myconstr is the database connection string
. Commandtext = "getuserlist" 'specifies the name of the stored process.
. Commandtype = 4' indicates that this is a stored Process
. Prepared = true' requires that the SQL command be compiled first
'Return value (total number of records)
. Parameters. append. createparameter ("return", 2, 4)
'Output parameter (total number of pages)
. Parameters. append. createparameter ("@ ipagecount", 3, 2)
'Input parameter (current page number)
. Parameters. append. createparameter ("@ ipage", 3, 1, 4, pagenow)
'Input parameter (number of records per page)
. Parameters. append. createparameter ("@ ipagesize", 3, 1, 4, pagesize)
Set myrst =. Execute
End
If myrst. State = 0 then' is not retrieved, myrst is disabled.
Recordcount =-1
Else
Myrst. Close 'Note: To obtain the parameter value, you must first close the record set object.
Recordcount = mycomm (0)
Pagecount = mycomm (1)
If CINT (pagenow)> = CINT (pagecount) Then pagenow = pagecount
End if
Set mycomm = nothing

'The following records are displayed:
If recordcount = 0 then
Response. Write "no record"
Elseif recordcount> 0 then
Myrst. Open
Do until myrst. EOF
......
Loop
'Page information is displayed below
......
Else 'recordcount =-1
Response. Write "parameter error"
End if

For the above Code, there is only one note: To obtain parameters when both the record set and parameters are returned, you must first disable the record set and enable it when using the record set.

7. The stored process that returns multiple Record Sets
This article first introduces the stored process that returns the record set. Sometimes, a stored process needs to return multiple record sets. In ASP, how can we get these record sets at the same time? To illustrate this problem, add two fields in the userinfo table: usertel and usermail, and set that only logon users can view the two fields.

/* Sp7 */
Create procedure DBO. getuserinfo
@ Userid int,
@ Checklogin bit
As
Set nocount on
Begin
If @ userid is null or @ checklogin is null return
Select Username
From DBO. [usrinfo]
Where userid = @ userid
-- If it is a logon user, use usertel and usermail
If @ checklogin = 1
Select usertel, usermail
From DBO. [userinfo]
Where userid = @ userid
Return
End
Go

The following is the ASP code:

'** The stored process that calls to return multiple record sets **
Dim checklg, userid, username, usertel, usermail
Dim mycomm, myrst
Userid = 1
'Checklogin () is a custom function to determine whether a visitor logs on
Checklg = checklogin ()
Set mycomm = server. Createobject ("ADODB. Command ")
With mycomm
. Activeconnection = myconstr 'myconstr is the database connection string
. Commandtext = "getuserinfo" 'specifies the name of the stored process.
. Commandtype = 4' indicates that this is a stored Process
. Prepared = true' requires that the SQL command be compiled first
. Parameters. append. createparameter ("@ userid", 3, 1, 4, userid)
. Parameters. append. createparameter ("@ checklogin", 11,1, 1, checklg)
Set myrst =. Execute
End
Set mycomm = nothing

'Value from the first record set
Username = myrst (0)
'Value from the second record set
If not myrst is nothing then
Set myrst = myrst. nextrecordset ()
Usertel = myrst (0)
Usermail = myrst (1)
End if
Set myrst = nothing

In the above Code, multiple record sets returned by the stored process are obtained using the nextrecordset method of the recordset object.

So far, this article provides a comprehensive description of the various situations in which ASP calls the storage process. Finally, in an ASPProgram.
In an ASP program, it is feasible to call multiple stored processes in at least three ways:
1. Create multiple command objects

Dim mycomm
Set mycomm = server. Createobject ("ADODB. Command ")
'Call a stored procedure 1
......
Set mycomm = nothing
Set mycomm = server. Createobject ("ADODB. Command ")
'Process 2 called
......
Set mycomm = nothing
......

2. Create only one command object. When the call is completed, clear its parameters.

Dim mycomm
Set mycomm = server. Createobject ("ADODB. Command ")
'Call a stored procedure 1
.....
'Clear parameters (assuming there are three parameters)
Mycomm. Parameters. Delete 2
Mycomm. Parameters. Delete 1
Mycomm. Parameters. Delete 0
'Call stored process 2 and clear parameters
......
Set mycomm = nothing

Note: The order of clearing parameters is the opposite of that of parameter Declaration. I don't know why.

3. Reset the parameter object using the refresh method of the parameters data set

Dim mycomm
Set mycomm = server. Createobject ("ADODB. Command ")
'Call a stored procedure 1
.....
'Reset all parameter objects contained in the parameters Dataset
Mycomm. Parameters. Refresh
'Process 2 called
.....
Set mycomm = nothing

It is generally thought that repeated object creation is a low efficiency method, but after testing (the test tool is Microsoft Application Center Test), the results are unexpected:
Method 2> = method 1> method 3
The running speed of method 2 is greater than or equal to method 1 (up to 4%). The running speed of these two methods is much higher than that of method 3 (up to 130%). Therefore, it is recommended that when there are many parameters, use method 1 and method 2 when there are few parameters.

It took me a day to finally build up some of my superficial experiences in calling the stored process in ASP. Some of them are the ones that I only know but do not know, and some may be wrong. However, these are all my own practices. You can accept it with criticism. I have different opinions. I hope I can tell you. thank you first.