There are many articles about ASP and stored procedures, but I doubt whether the author has really practiced them. When I was a beginner, I consulted a lot of relevant materials and found that many of the methods provided were not so practical. For simple applications, these materials may be helpful, but they are only limited to this, because they are basically the same, copying each other, and slightly more complicated applications are vague.
Now, I basically access SQL Server by calling stored procedures. Although the following words are not guaranteed to be absolutely correct, they are all summaries of practice, and I hope they can help everyone.
A stored procedure is just one or more SQL commands stored in a database as executable objects.
Definitions are always abstract. Stored procedures are actually a set of SQL statements that can perform certain operations, but these statements are placed in the database (only SQL Server here). If we create stored procedures and call them in ASP, we can avoid mixing SQL statements with ASP code. This has at least three advantages:
First, greatly improve efficiency. The execution speed of the stored procedure itself is fast, and calling the stored procedure can greatly reduce the number of interactions with the database.
Second, improve security. If the ASP code is mixed with SQL statements, once the code is broken, it also means that the library structure is broken.
Third, it is conducive to the reuse of SQL statements.
In ASP, stored procedures are usually called through command objects. According to different situations, this paper also introduces other calling methods. In order to facilitate the explanation, according to the input and output of stored procedures, the following simple classifications are made:
1. Stored procedures that return only a single recordset.
Suppose there are the following stored procedures (the purpose of this article is not to talk about T-SQL syntax, so the stored procedures only give code without explanation): /*SP 1*/
Create process dbo.getUserList
be like
Set nocount on
begin
select * from dbo。 [User information]
end
The above stored procedure gets all the records in the userinfo table and returns a recordset. The ASP code for calling this stored procedure through the command object is as follows:
* * Calling a stored procedure through a command object * *
DIM MyComm，MyRst
Set MyComm = Server. CreateObject("ADODB。 Command ")
Mycomm.activeconnection = myconstruct' myconstruct is a 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 that the SQL command be compiled first.
Set MyRst = MyComm. carry out
The recordset obtained by the Set MyComm = Nothing stored procedure is assigned to MyRst. Then you can operate MyRst.
In the above code, the CommandType property indicates the type of request, and its value and description are as follows:
-1 indicates that the type of the CommandText parameter cannot be determined.
1 indicates that CommandText is a common command type.
2 indicates that the CommandText parameter is an existing table name.
4 means that the CommandText parameter is the name of the stored procedure.
You can also call a stored procedure through a Connection object or a Recordset object, as follows: "* * Call a stored procedure through a Connection object * *
Tim Maikang, Meister
Set MyConn = Server. CreateObject("ADODB。 Connect ")
MyConn.open MyConStr 'MyConStr is a database connection string.
Set myrst = myconn. Execute ("getuserlist ",0,4)' The last interrupt has the same meaning as CommandType.
Set MyConn = Nothing '** Calling a stored procedure through a recordset object * *
DIM MyRst
Set MyRst = Server. CreateObject("ADODB。 Recordset ")
MyRst.open "getUserList "，MyConStr，0， 1，4
MyConStr is a database connection string, and the last interrupt has the same meaning as CommandType.
2. Stored procedures without input and output
Please look at the following stored procedure: /*SP2*/
Create process dbo.delUserAll
be like
Set nocount on
begin
Delete from dbo. [User information]
end
This stored procedure deletes all records in the userinfo table without any input or output. The calling method is basically the same as the above, except that you don't need to get the recordset: "* * Call the stored procedure through the command object * *"
DIM MyComm
Set MyComm = Server. CreateObject("ADODB。 Command ")
Mycomm.activeconnection = myconstruct' myconstruct is a database connection string.
MyComm。 CommandText = "delUserAll "'specifies the stored procedure name.
MyComm。 "CommandType = 4" indicates that this is a stored procedure.
MyComm。 "Prepared = true" requires that the SQL command be compiled first.
MyComm。 You don't need to get a recordset here.
Set MyComm = Nothing Of course, this stored procedure can also be called through the Connection object or the recordset object, but the Recordset object is created to obtain the Recordset. If no recordset is returned, it is best to use the Command object.
3. Stored procedures with return values
When doing operations similar to SP2, we should make full use of the powerful transaction processing function of SQL Server to maintain data consistency. Moreover, we may need the stored procedure to return to the execution state, so SP2 is modified as follows: /*SP3*/
Create process dbo.delUserAll
be like
Set nocount on
begin
Start trading
Delete from dbo. [User information]
If @@error=0
begin
Submit a transaction
Return 1
end
other
begin
Rollback transaction
Return 0
end
return
end
For stored procedures above go, delete successfully returns 1, otherwise 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 = myconstruct' myconstruct is a database connection string.
MyComm。 CommandText = "delUserAll "'specifies the stored procedure name.
MyComm。 "CommandType = 4" indicates that this is a stored procedure.
MyComm。 "Prepared = true" requires that the SQL command be compiled first.
Declare return value
Set Mypara = MyComm. CreateParameter("RETURN "，2，4)
MyComm。 Parameters. Additional MyPara
MyComm。 carry out
Get return value
DIM return value
Retail value = mycomm (0)' or retail value = mycomm. Parameter (0)
Set MyComm = Nothing.
In mycomm. Create parameters ("return", 2, 4), the meaning of each parameter is as follows:
The first parameter ("RETURE") is the parameter name. Parameter names can be set at will, but usually they should be the same as those declared in the stored procedure. Here is the return value, which I set to "return" before;
The second parameter (2) indicates the data type of the parameter. Please refer to ADO for specific type codes, and the common type codes are as follows:
adBigInt:20；
adBinary: 128；
ad boolean: 1 1；
adChar: 129；
adDBTimeStamp: 135；
ad empty:0；
ad integer:3；
adSmallInt:2；
adTinyInt: 16；
adVarChar:200；
For the return value, only integers can be used, 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 parameter value is described as follows:
0: The type cannot be determined; 1: enter 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 complicated code. In fact, set my para = mycomm. Create parameters ("return", 2, 4).
MyComm。 Parameters. Additional MyPara
It can be simplified to mycomm. Parameters. Add mycomm. Create parameter(“return ",2,4) can even be simplified, which will be explained later.
For stored procedures with parameters, they can only be called by the Command object (there are also materials that can be called by the Connection object or the Recordset object, but I haven't 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 with input and output parameters. For example, we want to get the user name of an ID user in the user information table. At this point, there is an input parameter-user ID and an output parameter-user name. The stored procedure to realize this function is as follows: /*SP4*/
Create process dbo.getUserName
@UserID int，
@UserName varchar(40) output
be like
Set nocount on
begin
Returns if @UserID is empty.
Select @ username = username.
From dbo. [User information]
Where userid=@UserID
return
end
Go calls the stored procedure with ASP code, as follows: "* * Calls the stored procedure with input and output parameters * *"
DIM MyComm, UserID, user name
UserID = 1
Set MyComm = Server. CreateObject("ADODB。 Command ")
Mycomm.activeconnection = myconstruct' myconstruct is a 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 that the SQL command be compiled first.
Declare parameters
MyComm。 Parameters.append MyComm。 CreateParameter("@UserID "，3， 1，4，UserID)
MyComm。 Parameters.append MyComm。 Create parameter(" @ username ",200, 2, 40)
MyComm。 carry out
Get a reference
User name = MyComm( 1)
Set MyComm = Nothing in the above code. As you can see, unlike declaring the return value, you need 5 parameters when declaring the input parameters and 4 parameters when declaring the output parameters. When declaring input parameters, these 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: parameter value.
It should be noted that 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 be the same as that defined in the stored procedure.
If the stored procedure has multiple parameters, ASP code will be very troublesome. You can use the with command to simplify the code:' * * Call the stored procedure with input and output parameters (simplify the code) * *
DIM MyComm, UserID, user name
UserID = 1
Set MyComm = Server. CreateObject("ADODB。 Command ")
Use MyComm
. Activeconnection = myconstr' myconstr is a database connection string.
. CommandText = "getUserName "'specifies the stored procedure name.
. "CommandType = 4" indicates that this is a stored procedure.
. "Prepared = true" requires that the SQL command be compiled first.
Parameter appending. CreateParameter("@UserID "，3， 1，4，UserID)
Parameter appending. Create parameter(" @ username ",200, 2, 40)
. carry out
End with ...
User name = MyComm( 1)
Set MyComm = Nothing.
If you want to get the user names of users 1 to 10 and 10, do you want to create the command object 10 times? That's not true. If you need to call the same stored procedure many times, just change the input parameters and you will get different outputs:' * * Call the same stored procedure many times * *
DIM MyComm, UserID, user name
User name = ""
Set MyComm = Server. CreateObject("ADODB。 Command ")
For UserID = 1 to 10.
Use MyComm
. Activeconnection = myconstr' myconstr is a database connection string.
. CommandText = "getUserName "'specifies the stored procedure name.
. "CommandType = 4" indicates that this is a stored procedure.
. "Prepared = true" requires that the SQL command be compiled first.
If UserID = 1
Parameter appending. CreateParameter("@UserID "，3， 1，4，UserID)
Parameter appending. Create parameter(" @ username ",200, 2, 40)
. carry out
other
Re-assign values to input parameters (at this time, input parameters and output parameters with unchanged parameter values do not need to be re-declared).
. Parameter ("@UserID") = UserID
. carry out
If ... it will be over.
End with ...
Username = username+mycomm (1)+",'Maybe you like to store it in an array.
then
Set MyComm = Nothing From the above code, we can see that when calling the same stored procedure repeatedly, we only need to re-assign the input parameters with changed values. This method can greatly reduce the amount of code when there are multiple input and output parameters and the value of only one input parameter changes at a time.
5. Stored procedure with return value, input parameters and output parameters.
As mentioned earlier, when calling a stored procedure, the order in which parameters are declared should be the same as that defined in the stored procedure. One more thing needs special attention: if a stored procedure has both return values and input and output parameters, the return values should be declared first.
To demonstrate the calling method in this case, let's improve the above example. We 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 the userid is a self-growing field). Depending on whether the user exists, the stored procedure returns different values. At this point, the stored procedure and ASP code are as follows: /*SP5*/
Create process dbo.getUserName
-In order to deepen the impression of "order", the definition order of the following two parameters is reversed.
@UserName varchar(40) output,
@UserID int
be like
Set nocount on
begin
Returns if @UserID is empty.
Select @ username = username.
From dbo. [User information]
Where userid=@UserID
if @ @ rowcount & gt0
Return 1
other
Return 0
return
end
Go '** calls a stored procedure with a return value, an input parameter and an output parameter * *
DIM MyComm, UserID, user name
UserID = 1
Set MyComm = Server. CreateObject("ADODB。 Command ")
Use MyComm
. Activeconnection = myconstr' myconstr is a database connection string.
. CommandText = "getUserName "'specifies the stored procedure name.
. "CommandType = 4" indicates that this is a stored procedure.
. "Prepared = true" requires that the SQL command be compiled first.
The return value should be declared first
Parameter appending. CreateParameter("RETURN "，2，4)
The declaration order of the following two parameters is reversed accordingly.
Parameter appending. Create parameter(" @ username ",200, 2, 40)
Parameter appending. CreateParameter("@UserID "，3， 1，4，UserID)
. carry out
End with ...
If MyComm(0) = 1, then
User name = MyComm( 1)
other
User name = "User does not exist"
If ... it will be over.
Set MyComm = Nothing.
6. Return the stored procedures of parameters and recordsets.
Sometimes, we need stored procedures to return both parameters and recordsets. For example, when using stored procedures for paging, we need to return two parameters, such as recordset and total data. The following stored procedures are used for paging: /*SP6*/
Create process dbo.getUserList
@ ipagecount int output,-total pages
@ ipageint,-current page number
@ I pagesizeint- the number of records per page.
be like
Set nocount on
begin
-Create a temporary table
Create table #t (ID int IDENTITY,-self-added field.
User id int,
User name varchar(40))
-Write data to a temporary table
Insert into #t
Select userid and username from dbo. [User information]
Sort by user id
-Total number of records obtained
declare @iRecordCount int
Set @iRecordCount = @@rowcount-determines the total number of pages.
If @iRecordCount%@iPageSize=0
SET @ IP agecount = CEILING(@ iRecordCount/@ IP agesize)
other
SET @ IP agecount = CEILING(@ iRecordCount/@ IP agesize)+ 1
-If the requested page number is greater than the total number of pages, the last page will be displayed.
IF @ iPage & gt@iPageCount
SELECT @iPage = @iPageCount-Determines 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-to get the current page record.
Select * from # t where ID & gt@iStart and ID<@ iend- delete temporary table.
DROP TABLE #t-Returns the total number of records.
return @iRecordCount
end
Enter the above stored procedure, enter the current page number and the number of records per page, and return the record set, total pages and total records of the current page. More typically, the total number of records is returned as a return value. The following is the ASP code for calling the stored procedure (the specific paging operation is omitted):' * * Calling the paged stored procedure * *
DIM pagenow，pagesize，pagecount，recordcount
DIM MyComm，MyRst
Pagenow = request ("pn")
Custom functions are used to verify natural numbers.
If CheckNar(pagenow) = false, pagenow = 1
Page size = 20
Set MyComm = Server. CreateObject("ADODB。 Command ")
Use MyComm
. Activeconnection = myconstr' myconstr is a database connection string.
. CommandText = "getUserList "'specifies the stored procedure name.
. "CommandType = 4" indicates that this is a stored procedure.
. "Prepared = true" requires that the SQL command be compiled first.
Return value (total number of records)
Parameter appending. CreateParameter("RETURN "，2，4)
Participation (total pages)
Parameter appending. CreateParameter("@iPageCount "，3，2)
Reference (current page number)
Parameter appending. CreateParameter("@iPage "，3， 1，4，pagenow)
Reference (number of records per page)
Parameter appending. CreateParameter("@iPageSize "，3， 1，4，PageSize)
Set MyRst =. carry out
End with ...
If MyRst.state = 0, data cannot be obtained, and MyRst is closed.
Record count =-1
other
MyRst.close' Note: To get parameter values, you need to close the recordset object first.
recordcount = MyComm(0)
pagecount = MyComm( 1)
If cint(pagenow)>=cint(pagecount) and then pagenow=pagecount.
If ... it will be over.
The following records will be displayed.
If record count = 0, then
Response. Write "no record"
Elseif recordcount & gt then 0
MyRst.open
Until my birthday. eof
......
ring
The paging information is as follows.
......
else 'recordcount=- 1
Response. Write down "Parameter Error"
End if For the above code, there is only one thing to explain: when returning both the recordset and the parameters, to get the parameters, you need to close the recordset first, and then open it when using the recordset.
7. Stored procedures that return multiple recordsets
This paper first introduces the stored procedure that returns a recordset. Sometimes, you need a stored procedure to return multiple recordsets. How to get these recordsets in ASP at the same time? To illustrate this problem, two fields are added to the userinfo table: usertel and usermail, and it is set that only logged-in users can view these two contents. /*SP7*/
Create process dbo.getUserInfo
@userid int，
@checklogin bit
be like
Set nocount on
begin
Returns if @userid is null or @checklogin is null.
Select user name
From dbo. [usrinfo]
Where userid=@userid
-If it is a logged-in user, take usertel and usermail.
if @checklogin= 1
Select user phone, user mail.
From dbo. [User information]
Where userid=@userid
return
end
The following is the ASP code:' * * Call a stored procedure that returns multiple recordsets * *
DIM checklg, user ID, user name, user phone number, user email.
DIM MyComm，MyRst
UserID = 1
Checklogin () is a user-defined function to determine whether a visitor logs in.
checklg = checklogin()
Set MyComm = Server. CreateObject("ADODB。 Command ")
Use MyComm
. Activeconnection = myconstr' myconstr is a database connection string.
. CommandText = "getUserInfo "'specifies the stored procedure name.
. "CommandType = 4" indicates that this is a stored procedure.
. "Prepared = true" requires that the SQL command be compiled first.
Parameter appending. CreateParameter("@userid "，3， 1，4，userid)
Parameter appending. CreateParameter("@checklogin "， 1 1， 1， 1，checklg)
Set MyRst =. carry out
End with ...
"Set MyComm = Nothing" gets the value from the first recordset.
UserName = MyRst(0)
Get values from the second recordset.
If it's not mine, it's nothing.
Set MyRst = MyRst. NextRecordset()
UserTel = MyRst(0)
UserMail = MyRst( 1)
If ... it will be over.
In the code of Set MyRst = Nothing above, the nextRecordset method of the recordset object is used to obtain multiple recordsets returned by the stored procedure.
I don't know if it is.