ASP and stored procedure practice (example)

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. Although the following text cannot be guaranteed to be absolutely correct, it 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, greatly improve 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 classifications are made according to the input and output of the stored process:
1. A stored procedure that only returns a single record set
Suppose there is the following stored procedure (the purpose of this article is not to describe T-SQL syntax, so the stored procedure only gives the code without explanation):

/*SP1*/
CREATE PROCEDURE dbo.getUserList
as
set nocount on
begin
select * from dbo.[userinfo]
end

of go

obtains all the 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 'Indicates that this is a stored procedure
MyComm.Prepared = true 'Requires SQL commands to be compiled first
Set MyRst = MyComm.Execute
Set MyComm = Nothing

The record set obtained by the stored procedure 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 CommandText 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

. The stored procedure can also be called 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

'**Call stored procedure through 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. Stored procedures without input and output
Please look at the following stored procedure:

/*SP2*/
CREATE PROCEDURE dbo.delUserAll
as
set nocount on
begin
delete from dbo.[userinfo]
end

go

stored procedure deletes all records in the userinfo table without any input or output. The calling 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 'Indicates that this is a stored procedure
MyComm.Prepared = true 'Requires SQL commands 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. Stored procedures with return values
When performing SP2-like operations, you 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

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
'Declare 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, I usually set it to "RETURE";
The second parameter (2) indicates the data type of the parameter. Please refer to the ADO reference for specific type codes. Common type codes are given below:
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 are reserved values;
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 parameters; 2: Input parameters; 3: Input or output parameters; 4: Return value

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

Set Mypara = MyComm.CreateParameter("RETURN",2,
MyComm.Parameters.Append MyPara

can be simplified to

MyComm.Parameters.Append MyComm.CreateParameter("RETURN",2,4)

and can even continue to be simplified, 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. Stored procedures with input parameters and output parameters
The return value 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

to call the stored procedure is as follows:

'**Call a 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 commands to be compiled first
'Declare 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")
withMyComm
.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
.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 usernames 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
withMyComm
.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 values ​​to 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 array storage
next
Set MyComm = Nothing

As can be seen 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 has multiple input and output parameters, and only one is called each time. The amount of code can be greatly reduced when the value of the input parameter changes.

5. Stored procedures with return values, input parameters, and output parameters at the same time
As mentioned earlier, when calling a stored procedure, the order in which 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 "order", reverse the order of definition 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

'**Call a stored procedure with return value, input parameters, and output parameters**
DIM MyComm,UserID,UserName
UserID = 1
Set MyComm = Server.CreateObject("ADODB.Command")
withMyComm
.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
'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 procedure that returns parameters and recordset at the same time
Sometimes, we need the stored procedure to return parameters and record sets at the same time. For example, when using stored procedures for paging, we need to return parameters such as record sets and the total amount of data at the same time. The following is a stored procedure for paging:

/*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 temporary table
create table #t (ID int IDENTITY, --auto-increment field
userid int,
username varchar(40))
--Write data to 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, the last page will be displayed
IF @iPage > @iPageCount
SELECT @iPage = @iPageCount

-- determine the beginning and end 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 temporary table
DROP TABLE #t

--returns the total number of records
return @iRecordCount
end

In the above stored procedure,

go

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 function used to verify natural numbers
if CheckNar(pagenow) = false then pagenow = 1
pagesize = 20
Set MyComm = Server.CreateObject("ADODB.Command")
withMyComm
.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)
SetMyRst = .Execute
end with
if MyRst.state = 0 then 'No data was obtained, MyRst is closed
recordcount = -1
else
MyRst.close 'Note: To obtain parameter values, you need to close the recordset object first
recordcount = MyComm(0)
pagecount = MyComm(1)
if cint(pagenow)>=cint(pagecount) then pagenow=pagecount
end if
Set MyComm = Nothing

'Display records below
if recordcount = 0 then
Response.Write "No record"
elseif recordcount > 0 then
MyRst.open
do until MyRst.EOF
...
loop
'The following displays 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 recordset and parameters at the same time, if you want to obtain the parameters, you need to close the recordset first, and then open it when using the recordset.

7. Stored procedure that returns multiple record sets
The first thing this article introduces is the stored procedure that returns a recordset. 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 you are a logged in user, take usertel and usermail
if @checklogin=1
select usertel,usermail
from dbo.[userinfo]
where userid=@userid
return
end

following

is the ASP code:

'**Call a stored procedure that returns multiple recordsets**
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")
withMyComm
.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 SQL commands to be compiled first
.Parameters.append .CreateParameter("@userid",3,1,4,UserID)
.Parameters.append .CreateParameter("@checklogin",11,1,1,checklg)
SetMyRst = .Execute
end with
Set MyComm = Nothing

'Get the value from the first 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, at least the following three methods are feasible to call multiple stored procedures:
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. Only create a Command object, and when ending a call, clear its parameter

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 parameters
...
Set MyComm = Nothing

At this time, please note: the order of clearing parameters is opposite to the order of parameter declaration. I don’t know the reason.

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 two
.....
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%). Therefore, it is recommended to use method 1 when there are many parameters. If not, use method 2.

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.