There are many articles about ASP and stored procedures (Stored Procedures), but I doubt whether the authors have actually practiced them. When I was a beginner, I consulted a lot of relevant information and found that many of the methods provided were not the same in practice. For simple applications, these materials may be helpful, but they are limited to this, because they are basically the same and copy each other. For slightly more complex applications, they are all unclear.
Now, I basically access SQL Server by calling stored procedures. The following text is a summary of practice. I hope it will be helpful to everyone.
A stored procedure is one or more SQL commands stored in the database as executable objects.
Definitions are always abstract. A stored procedure is actually a set of SQL statements that can complete certain operations, but this set of statements is placed in the database (here we only talk about SQL Server). If we create stored procedures and call stored procedures in ASP, we can avoid mixing SQL statements with ASP code. There are at least three benefits to doing this:
First, it greatly improves efficiency. The execution speed of the stored procedure itself is very fast, and calling the stored procedure can greatly reduce the number of interactions with the database.
Second, improve safety. If you mix SQL statements in ASP code, once the code is compromised, it also means that the library structure is compromised.
Third, it is conducive to the reuse of SQL statements.
In ASP, stored procedures are generally called through the command object. Depending on the situation, this article also introduces other calling methods. For the convenience of explanation, the following simple classification is made based on the input and output of the stored procedure:
1. The stored procedure that only returns a single record set
assumes the following stored procedure (the purpose of this article is not to describe T-SQL syntax, so the stored procedure only gives the code , no explanation):
/*SP1*/
CREATE PROCEDURE dbo.getUserList
as
set nocount on
begin
select * from dbo.[userinfo]
end
go
The above stored procedure obtains all records in the userinfo table and returns a record set. The ASP code for calling the stored procedure through the command object is as follows:
'**Calling the stored procedure 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" 'Specify the stored procedure name
MyComm.CommandType = 4 'Indicate that this is a stored procedure
MyComm.Prepared = true 'Require the SQL command to be compiled first
Set MyRst = MyComm.Execute
Set MyComm = Nothing
stored procedure The obtained record set is assigned to MyRst. Next, MyRst can be operated.
In the above code, the CommandType attribute indicates the type of request. The value and description are as follows:
-1 indicates that the type of the CommandText parameter cannot be determined
1 indicates that the CommandText parameter is a general command type
2 indicates that the CommandText parameter is an existing table name
4 indicates that the CommandText parameter is The name of a stored procedure
can also call the stored procedure through the Connection object or Recordset object. The methods are as follows:
'**Call the stored procedure 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",0,4) 'The last parameter has the same meaning as CommandType
Set MyConn = Nothing
'**Calling the stored procedure 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 has the same meaning as CommandType
2. There is no input and output storage
Please see the following stored procedurefor the process
:
/*SP2*/
CREATE PROCEDURE dbo.delUserAll
as
set nocount on
begin
delete from dbo.[userinfo]
end
go
This stored procedure deletes all records in the userinfo table without any input or output. Call The method is basically the same as mentioned above, except that there is no need to obtain the record set:
'**Call the stored procedure through the Command object**
DIM MyComm
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = MyConStr 'MyConStr is the database Connection string
MyComm.CommandText = "delUserAll" 'Specify the stored procedure name
MyComm.CommandType = 4 'Indicate that this is a stored procedure
MyComm.Prepared = true 'Require the SQL command to be compiled first
MyComm.Execute 'No need to obtain the record set here
Set MyComm = Nothing
Of course, such stored procedures can also be called through the Connection object or Recordset object, but the Recordset object is created to obtain the recordset. If the recordset is not returned, it is better to use the Command object.
3.When performing operations similar to SP2,
stored procedures with return values
should make full use of the powerful transaction processing capabilities of SQL Server to maintain data consistency.Moreover, we may need the stored procedure to return the execution status. To this end, 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 procedure returns 1 when delete is executed successfully, otherwise it returns 0 and performs rollback operation. In order to get the return value in ASP, you need to use the Parameters collection to declare the parameters:
'**Call the stored procedure with the return value and get the return value**
DIM MyComm,MyPara
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = MyConStr 'MyConStr is the database connection string
MyComm.CommandText = "delUserAll" 'Specify the stored procedure name
MyComm.CommandType = 4 'Indicates that this is a stored procedure
MyComm.Prepared = true 'Requires SQL commands to be compiled first
' statement Return value
Set Mypara = MyComm.CreateParameter("RETURN",2,4)
MyComm.Parameters.Append MyPara
MyComm.Execute
'Get the return value
DIM retValue
retValue = MyComm(0) 'or retValue = MyComm.Parameters(0)
Set MyComm = Nothing
In MyComm.CreateParameter("RETURN",2,4), the meaning of each parameter is as follows:
The first parameter ("RETURE") is the parameter name. The parameter name can be set arbitrarily, but generally it should be the same as the parameter name declared in the stored procedure. Here is the return value, which I usually set to "RETURE";
the second parameter (2) indicates the data type of the parameter. For specific type codes, please refer to the ADO reference. The following is a commonly used type code:
adBigInt: 20 ;
adBinary : 128 ;
adBoolean: 11 ;
adChar: 129 ;
adDBTimeStamp: 135 ;
adEmpty: 0 ;
adInteger: 3 ;
adSmallInt: 2 ;
adTinyInt: 16 ;
adVarChar: 200 ;
For the return value, only integer values can be taken, and -1 to - 99 is a reserved value;
the third parameter (4) indicates the nature of the parameter, where 4 indicates that this is a return value. The description of the value of this parameter is as follows:
0: The type cannot be determined; 1: Input parameter; 2: Input parameter; 3: Input or output parameter; 4: Return value
The ASP code given above should be said to be a complete code, also That is the most complex code. In fact,
Set Mypara = MyComm.CreateParameter("RETURN",2,4)
MyComm.Parameters.Append MyPara
can be simplified to
MyComm.Parameters.Append MyComm.CreateParameter("RETURN",2,4)
or even You can continue to simplify, which will be explained later.
For stored procedures with parameters, they can only be called using the Command object (there is also information that they can be called through the Connection object or Recordset object, but I have not tried it).
4. The return value of a stored procedure with input parameters and output parameters
is actually a special output parameter. In most cases, we use stored procedures that have both input and output parameters. For example, we want to obtain the user name of a user with a certain ID in the user information table. At this time, there is an input parameter----user ID. , and an output parameter----user name. The stored procedure to implement 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 to call the stored procedure is as follows:
'**Call the stored procedure 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" 'Specify the stored procedure name
MyComm.CommandType = 4 'Indicates that this is a stored procedure
MyComm.Prepared = true 'Requires SQL Compile the command first
and declare the parameters
MyComm.Parameters.append MyComm.CreateParameter("@UserID",3,1,4,UserID)
MyComm.Parameters.append MyComm.CreateParameter("@UserName",200,2,40)
MyComm. Execute
'Get the parameters
UserName = MyComm(1)
Set MyComm = Nothing
In the above code, you can see that, unlike declaring the return value, 5 parameters are required when declaring input parameters, and 4 parameters are required when 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 an output parameter, there is no last parameter: the parameter value.
Special attention should be paid to: when declaring parameters, the order must be the same as that defined in the stored procedure, and the data type and length of each parameter must also be the same as those defined in the stored procedure.
If the stored procedure has multiple parameters, the ASP code will appear cumbersome. You can use the with command to simplify the code:
'**Call a stored procedure 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" 'Specify the stored procedure name.CommandType
= 4 'Indicates that this is a stored procedure.Prepared
= true 'Require the SQL command to be compiled first.Parameters.append
.CreateParameter("@UserID",3,1,4,UserID)
.Parameters.append .CreateParameter("@UserName",200,2,40)
.Execute
end with
UserName = MyComm(1)
Set MyComm = Nothing
If we want to get the user names of 10 users with IDs 1 to 10, do we need to create the Command object 10 times? No. If you need to call the same stored procedure multiple times, just change the input parameters and you will get different outputs:
'**Multiple calls to the same stored procedure**
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" 'Specify the stored procedure name.CommandType
= 4 'Indicates that this is a stored procedure.Prepared
= true 'Requires SQL commands to be compiled first
if UserID = 1 then
.Parameters.append .CreateParameter("@UserID",3,1,4,UserID)
.Parameters.append .CreateParameter("@UserName",200,2,40 )
.Execute
else
'Reassign the input parameters (input parameters and output parameters whose parameter values do not change at this time do not need to be re-declared)
.Parameters("@UserID") = UserID
.Execute
end if
end with
UserName = UserName + MyComm( 1) + "," 'Maybe you like to use an array to store
next
Set MyComm = Nothing.
Asyou can see from the above code: when calling the same stored procedure repeatedly, you only need to reassign the input parameters whose values have changed. This method is When there are multiple input and output parameters, and the value of only one input parameter changes each time it is called, the amount of code can be greatly reduced.
5. Stored procedures that have return values, input parameters, and output parameters at the same time. As
mentioned earlier, when calling a stored procedure, the order in which the parameters are declared must be the same as the order defined in the stored procedure. Another point to pay special attention to: if the stored procedure has both a return value and input and output parameters, the return value must be declared first.
To demonstrate the calling method in this case, let's improve the above example. Still get the user name of the user with ID 1, but it is possible that the user does not exist (the user has been deleted, and userid is a self-increasing field). The stored procedure returns different values depending on whether the user exists or not. At this time, the stored procedure and ASP code are as follows:
/*SP5*/
CREATE PROCEDURE dbo.getUserName
--In order to deepen the impression of "sequence", reverse the definition order of the following two parameters
@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
'**The call has both return value and input Stored procedures for 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" 'Specify the stored procedure name.CommandType
= 4 'Indicates that this is a stored procedure
. Prepared = true 'Requires the SQL command to be compiled first
' The return value must be declared first.Parameters.Append
.CreateParameter("RETURN",2,4)
'The declaration order of the following two parameters is also reversed accordingly.Parameters.append
.CreateParameter("@UserName",200,2,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 exist"
end if
Set MyComm = Nothing
6. Stored procedures that return parameters and recordsets at the same time
Sometimes, we need stored procedures Return parameters and record sets at the same time. For example, when using stored procedures for paging, parameters such as record sets and total data volume must be returned at the same time. The following is a stored procedure 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 into the temporary table
insert into #t
select userid,username from dbo.[UserInfo]
order by userid
--Get 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 number of pages, display the last page
IF @iPage > @iPageCount
SELECT @iPage = @iPageCount
--Determine the beginning 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
--Get the current page record
select * from # t where ID> @iStart and ID <@iEnd
--Delete the temporary table
DROP TABLE #t
--Return the total number of records
return @iRecordCount
end
goIn
the above stored procedure, enter the current page number and the number of records per page, and return the current page The record set, the total number of pages and the total number of records. 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 procedure (the specific paging operation is omitted):
'**Call the paging stored procedure**
DIM pagenow,pagesize,pagecount,recordcount
DIM MyComm,MyRst
pagenow = Request("pn")
'Custom The function is used to verify natural numbers
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 " 'Specify the stored procedure name.CommandType
= 4 'Indicates that this is a stored procedure
. Prepared = true 'Requires SQL commands to be compiled first
' Return value (total number of records)
.Parameters.Append .CreateParameter("RETURN",2,4 )
'Output parameters (total number of pages)
.Parameters.Append .CreateParameter("@iPageCount",3,2)
'Input parameters (current page number)
.Parameters.append .CreateParameter("@iPage",3,1,4 ,pagenow)
'Input parameters (number of records per page)
.Parameters.append .CreateParameter("@iPageSize",3,1,4,pagesize)
Set MyRst = .Execute
end with
if MyRst.state = 0 then 'Not obtained Data, MyRst close
recordcount = -1
else
MyRst.close 'Note: To obtain the parameter value, you need to close the record set object first
recordcount = MyComm(0)
pagecount = MyComm(1)
if cint(pagenow)> =cint(pagecount) then pagenow=pagecount
end if
Set MyComm = Nothing
'The following displays the record
if recordcount = 0 then
Response.Write "No record"
elseif recordcount > 0 then
MyRst.open
do until MyRst.EOF
......
loop
'The following displays the paging Information
...
else 'recordcount=-1
Response.Write "Parameter error"
end if
Regarding the above code, there is only one point that needs to be explained: when returning the record set and parameters at the same time, if you want to obtain the parameters, you need to close the record set first , and then open the recordset when using it.
7. Stored procedures that return multiple record sets
The first thing this article introduces is the stored procedures that return record sets. Sometimes, a stored procedure is required to return multiple record sets. In ASP, how to obtain these record sets at the same time? In order to illustrate this problem, add two fields to the userinfo table: usertel and usermail, and set that only logged-in users can view these two contents.
/*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 Log in user, get usertel and usermail
if @checklogin=1
select usertel,usermail
from dbo.[userinfo]
where userid=@userid
return
end
goThe
following is the ASP code:
'**Call a stored procedure that returns multiple record sets**
DIM checklg,UserID,UserName,UserTel,UserMail
DIM MyComm,MyRst
UserID = 1
'checklogin() is a custom function to determine whether the visitor is logged in
checklg = checklogin()
Set MyComm = Server.CreateObject("ADODB.Command")
with MyComm
.ActiveConnection = MyConStr 'MyConStr is the database connection string.CommandText
= "getUserInfo" 'Specify the stored procedure name.CommandType
= 4 'Indicates that this is a stored procedure.Prepared
= true 'Requires the SQL command to be compiled first.Parameters.append
.CreateParameter ("@userid",3,1,4,UserID)
.Parameters.append .CreateParameter("@checklogin",11,1,1,checklg)
Set MyRst = .Execute
end with
Set MyComm = Nothing
'From the first Get the value from the record set
UserName = MyRst(0)
'Get the 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, the NextRecordset method of the Recordset object is used to obtain multiple record sets returned by the stored procedure.
So far, this article has given a relatively comprehensive explanation of various situations in which ASP calls stored procedures. Finally, let's talk about the different methods of calling multiple stored procedures in an ASP program.
In an ASP program, it is feasible to call multiple stored procedures in at least the following three ways:
1. Create multiple Command objects
DIM MyComm
Set MyComm = Server.CreateObject("ADODB.Command")
'Call stored procedure one
.. ....
Set MyComm = Nothing
Set MyComm = Server.CreateObject("ADODB.Command")
'Call stored procedure two
...
Set MyComm = Nothing
...
2. Create only one Command object, When ending a call, clear its parameters
DIM MyComm
Set MyComm = Server.CreateObject("ADODB.Command")
'Call stored procedure one
...
'Clear parameters (assuming there are three parameters)
MyComm.Parameters.delete 2
MyComm. Parameters.delete 1
MyComm.Parameters.delete 0
'Call stored procedure two and clear the parameters
...
Set MyComm = Nothing
At this time, please note: the order of clearing parameters is opposite to the order of parameter declaration. The reason is that I don't know. Know.
3. Use the Refresh method of the Parameters data collection to reset the Parameter object
DIM MyComm
Set MyComm = Server.CreateObject("ADODB.Command")
'Call stored procedure one
...
'Reset all Parameter objects contained in the Parameters data collection
MyComm.Parameters.Refresh
'Call stored procedure 2
...
Set MyComm = Nothing
It is generally believed that repeatedly creating objects is a less efficient 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 about 4% higher). The running speed of these two methods is much faster than Method 3 (up to 130%), so it is recommended to When there are many parameters, method 1 is used; when there are few parameters, method 2 is used.
It took me a day to finally put into writing some of my superficial experience in calling stored procedures in ASP. Among them, some of them I only know the effects but not the causes, and some may be wrong, but these are all through my personal practice. Please accept it critically, readers. If you have any different opinions, please let me know. Thanks in advance.