Summary of experience in Dynamic Web pages: ASP and stored procedure practices (example)

Stored Procedure | dynamic | Web page

ASP and stored procedures (Stored procedures) article a lot, but I doubt whether the authors really practice. I have consulted a lot of relevant data at the beginning of the class, and found that many of the methods provided are not the case. For simple applications, this information may be helpful, but it is limited to this, because they are simply stereotyped, mutual plagiarism, a slightly more complex application, all vague.

Now, I basically access SQL Server by calling stored procedures, the following text is not guaranteed to be absolutely correct, but it is a summary of the practice, I hope to be helpful to everyone.

A stored procedure is one or more SQL commands that are stored as executable objects in the database.

Definitions are always abstract. A stored procedure is actually a set of SQL statements that can do something, except that the set of statements is in the database (here we 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 stored procedure itself executes very quickly, and invoking a stored procedure can significantly reduce the number of interactions with the database.

Second, improve security. If the SQL statement mixed in the ASP code, once the code compromised, but also means that the library structure compromised.

Third, to facilitate the reuse of SQL statements.

In ASP, the stored procedure is usually invoked through the command object, and the other invocation methods are described in this article according to different situations. To facilitate the description, according to the input and output of the stored procedure, make the following simple categories:

1. A stored procedure that returns only a single recordset

The following stored procedures are assumed (the purpose of this article is not to tell the T-SQL syntax, so the stored procedure gives the code only, not the description):

/*sp1*/
CREATE PROCEDURE Dbo.getuserlist
As
SET NOCOUNT ON
Begin
SELECT * FROM dbo. [UserInfo]
End
Go

The above stored procedure gets all the records in the UserInfo table and returns a recordset. The ASP code that invokes the stored procedure through the Command object is as follows:

' * * Call stored procedure via command object * *
DIM Mycomm,myrst
Set Mycomm = Server.CreateObject ("Adodb.command")
mycomm.activeconnection = Myconstr ' myconstr is a database connection string
Mycomm.commandtext = "Getuserlist" ' specifies stored procedure name
Mycomm.commandtype = 4 ' indicates that this is a stored procedure
Mycomm.prepared = True ' requires that SQL commands be compiled in advance
Set Myrst = Mycomm.execute
Set Mycomm = Nothing

The recordset obtained by the stored procedure is assigned to Myrst, and then the Myrst can be manipulated.
In the above code, the CommandType property indicates the type of the request, and 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 generic command type
2 indicates that the CommandText parameter is a table name that exists
4 indicates that the CommandText parameter is the name of a stored procedure

You can also invoke stored procedures by connection objects or Recordset objects, respectively, by using the following methods:

' * * Invoke stored procedure via connection object * *
DIM Myconn,myrst
Set myconn = Server.CreateObject ("ADODB. Connection ")
MyConn.Open Myconstr ' myconstr is a database connection string
Set Myrst = Myconn.execute ("Getuserlist", 0,4) ' last argument meaning same as CommandType
Set myconn = Nothing

' * * Call stored procedure via 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 argument has the same meaning as CommandType

2. Stored procedures without input and output
Please see the following stored procedures:

/*sp2*/
CREATE PROCEDURE Dbo.deluserall
As
SET NOCOUNT ON
Begin
Delete FROM dbo. [UserInfo]
End
Go

The stored procedure deletes all records in the UserInfo table, without any input or output, and the invocation method is essentially the same as the above, except that the recordset is not obtained:

' * * Call stored procedure via command object * *
DIM Mycomm
Set Mycomm = Server.CreateObject ("Adodb.command")
mycomm.activeconnection = Myconstr ' myconstr is a database connection string
Mycomm.commandtext = "Deluserall" ' specifies stored procedure name
Mycomm.commandtype = 4 ' indicates that this is a stored procedure
Mycomm.prepared = True ' requires that SQL commands be compiled in advance
Mycomm.execute ' There's no need to get the recordset here again
Set Mycomm = Nothing

This type of stored procedure can, of course, be invoked by connection object or Recordset object, although the Recordset object is created to get the recordset, or to use the Command object if no Recordset is returned.

3. Stored procedures with return values
When doing something like SP2, you should take full advantage of SQL Server's powerful transaction processing capabilities to maintain data consistency. Also, we may need the stored procedure to return to execution, and to this end, modify the 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 stored procedure above returns 1 when the delete is executed smoothly, otherwise it returns 0 and rolls back the operation. In order to get the return value in ASP, you need to use the Parameters collection to declare the parameter:

' * * Call stored procedure with return value and get return value * *
DIM Mycomm,mypara
Set Mycomm = Server.CreateObject ("Adodb.command")
mycomm.activeconnection = Myconstr ' myconstr is a database connection string
Mycomm.commandtext = "Deluserall" ' specifies stored procedure name
Mycomm.commandtype = 4 ' indicates that this is a stored procedure
Mycomm.prepared = True ' requires that SQL commands be compiled in advance
' 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 meanings of each parameter are as follows:
The first argument ("Reture") is the name of the parameter. Parameter names can be set arbitrarily, but generally should be the same as the name of the parameter declared in the stored procedure. Here is the return value, which I am accustomed to set to "Reture";
The second parameter (2) indicates the data type of the parameter, the specific type code refer to the ADO reference, and the following are the commonly used type codes:
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 the shape is taken, and-1 to-99 is the reserved value;
The third argument (4) indicates the property of the parameter, where 4 indicates that it is a return value. The value of this parameter is described as follows:
0: type cannot be determined; 1: input parameters; 2: input parameters; 3: input or output parameters; 4: return value

The above mentioned ASP code, should be said to be complete code, 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)

It can even continue to be simplified and will be explained later.
For stored procedures with parameters, it can only be invoked using the Command object (there is also data that can be invoked through the Connection object or Recordset object, but I haven't tried it).

4. Stored procedures with input parameters and output parameters
The return value is actually a special input parameter. In most cases, we use stored procedures that have both input and output parameters, such as the user name of an ID user in the User Information table, an input parameter----user ID, and an output parameter----user name. The stored procedures for implementing this feature are as follows:

/*sp4*/
CREATE PROCEDURE Dbo.getusername
@UserID int,
@UserName varchar () 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 that invokes the stored procedure is as follows:

' * * Call stored procedures with input and OUTPUT parameters * *
DIM Mycomm,userid,username
UserID = 1
Set Mycomm = Server.CreateObject ("Adodb.command")
mycomm.activeconnection = Myconstr ' myconstr is a database connection string
Mycomm.commandtext = "GetUserName" ' specifies stored procedure name
Mycomm.commandtype = 4 ' indicates that this is a stored procedure
Mycomm.prepared = True ' requires that SQL commands be compiled in advance
' 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 argument
UserName = Mycomm (1)
Set Mycomm = Nothing

In the above code, you can see that, unlike declaring a return value, you need 5 parameters to declare an input parameter, and 4 parameters to declare an output parameter. When declaring an input parameter, 5 parameters are: Parameter name, parameter data type, parameter type, data length, parameter value. When declaring an input parameter, there is no last argument: parameter value.

It is important to note that when declaring a parameter, the order must be the same as the order defined in the stored procedure, and the data type and length of each parameter will be the same as defined in the stored procedure.

If the stored procedure has more than one parameter, the ASP code becomes cumbersome, and you can use the WITH command to simplify the code:

' * * Call stored procedures 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 a database connection string
. CommandText = "GetUserName" ' specifies stored procedure name
. CommandType = 4 ' indicates that this is a stored procedure
. Prepared = True ' requires that SQL commands be compiled in advance
. 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 were to get a username of 1 to 10, 10 users, would you like to create a 10 command object? No, if you need to call the same stored procedure multiple times, just change the input parameters and you get different input parameters:

' * * Calls the same stored procedure multiple times * *
DIM Mycomm,userid,username
UserName = ""
Set Mycomm = Server.CreateObject ("Adodb.command")
For UserID = 1 to 10
With Mycomm
. ActiveConnection = Myconstr ' myconstr is a database connection string
. CommandText = "GetUserName" ' specifies stored procedure name
. CommandType = 4 ' indicates that this is a stored procedure
. Prepared = True ' requires that SQL commands be compiled in advance
If UserID = 1 Then
. Parameters.Append. CreateParameter ("@UserID", 3,1,4,userid)
. Parameters.Append. CreateParameter ("@UserName", 200,2,40)
. Execute
Else
' re-assigning values to the parameters (at which point the parameter values do not change and the argument does not have to be declared again)
. Parameters ("@UserID") = UserID
. Execute
End If
End With
UserName = UserName + mycomm (1) + "," ' Maybe you like to use array storage
Next
Set Mycomm = Nothing

As you can see from the above code: when you call the same stored procedure repeatedly, you only need to reassign the input parameter that changes the value, 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 each call.

5. Stored procedures with return values, input parameters, and output parameters
As mentioned earlier, when you call a stored procedure, the order in which you declare the parameters is the same as the order defined in the stored procedure. It is also important to note that if the stored procedure has both a return value and an input or output parameter, the return value is first declared.
To demonstrate the invocation method in this case, let's improve on the example above. Or 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 the field that grew). Stored procedures return different values depending on whether the user is present or not. At this point, the stored procedure and ASP code are as follows:

/*sp5*/
CREATE PROCEDURE Dbo.getusername
--in order to deepen the impression of "order", the following two parameters are defined in reverse order
@UserName varchar () 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 stored procedures with return values, input parameters, output parameters * *
DIM Mycomm,userid,username
UserID = 1
Set Mycomm = Server.CreateObject ("Adodb.command")
With Mycomm
. ActiveConnection = Myconstr ' myconstr is a database connection string
. CommandText = "GetUserName" ' specifies stored procedure name
. CommandType = 4 ' indicates that this is a stored procedure
. Prepared = True ' requires that SQL commands be compiled in advance

' return value to be declared first
. Parameters.Append. CreateParameter ("return", 2,4)
' The declaration order of the following two parameters is also reversed
. 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. Return parameters and Recordset stored procedures at the same time
Sometimes, we need stored procedures to return both parameters and recordsets, such as when using a stored procedure paging, returning both the recordset and the total amount of data. The following is a stored procedure that handles paging:

/*sp6*/
CREATE PROCEDURE Dbo.getuserlist
@iPageCount int OUTPUT,--Total 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,--self-added field
UserID int,
Username varchar (40))
--write data to a temporary table
INSERT INTO #t
Select Userid,username from dbo. [UserInfo]
ORDER BY UserID

--Total number of records obtained
DECLARE @iRecordCount int
Set @iRecordCount = @ @rowcount

--Determine total pages
IF @iRecordCount% @iPageSize =0
SET @iPageCount =ceiling (@iRecordCount/@iPageSize)
ELSE
SET @iPageCount =ceiling (@iRecordCount/@iPageSize) +1

--Displays the last page if the requested page number is greater than the total pages
IF @iPage > @iPageCount
SELECT @iPage = @iPageCount

--Determine the history of the current page
DECLARE @iStart int--start record
DECLARE @iEnd int--end record
SELECT @iStart = (@iPage-1) * @iPageSize
SELECT @iEnd = @iStart + @iPageSize + 1

--Take the current page record
SELECT * from #t where id> @iStart and id< @iEnd

--Delete temporary tables
DROP TABLE #t

--Returns the total number of records
Return @iRecordCount
End
Go

In the previous stored procedure, enter the current page number and the number of records per page, returning the recordset, total pages, and total number of records for the current page. To be more typical, the total number of records is returned in the form of a return value. The following is the ASP code that invokes the stored procedure (omitted for specific paging operations):

* * Call Paging stored Procedure * *
Dim Pagenow,pagesize,pagecount,recordcount
Dim Mycomm,myrst
Pagenow = Request ("pn")
' The custom function is used to validate the natural number
if Checknar (pagenow) = False Then Pagenow = 1
pagesize =
Set Mycomm = Server.CreateObject ("ADO Db. Command ")

with 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 the SQL command to be compiled in advance
' return value (Total records)
. Parameters.Append. CreateParameter ("return", 2,4)
' Outbound parameters (total pages)
. Parameters.Append. CreateParameter ("@iPageCount", 3,2)
' Enter parameter (current page number)
. Parameters.Append. CreateParameter ("@iPage", 3,1,4,pagenow)
' Enter parameters (number of records per page)
. Parameters.Append. CreateParameter ("@iPageSize", 3,1,4,pagesize)
Set Myrst =. Execute
End With
If myrst.state = 0 Then ' does not fetch data, Myrst closes
RecordCount =-1
Else
Myrst.close ' NOTE: To get the value of a parameter, You must first close the Recordset object
RecordCount = Mycomm (0)
PageCount = Mycomm (1)
If CInt (pagenow) >=cint (PageCount) Then PageCount
End If
Set Mycomm = Nothing

' Show records below
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

For the above code, there is only one point to note: When you return both a recordset and a parameter, to get a parameter, you close the recordset, and then open the recordset when you use it.

7. Return stored procedures for multiple recordsets
This article first describes the stored procedures that return a recordset. Sometimes, you need a stored procedure to return multiple recordsets, how do you get them at the same time in the ASP? To illustrate this issue, add two fields to the UserInfo table: Usertel and Usermail, and set only the Logged-in users to see both.

/*sp7*/
CREATE PROCEDURE Dbo.getuserinfo
@userid int,
@checklogin bit
As
SET NOCOUNT ON
Begin
If @userid is null or @checklogin be null return
Select username
FROM dbo. [Usrinfo]
where userid= @userid
--If you are a registered user, take Usertel and Usermail
If @checklogin =1
Select Usertel,usermail
FROM dbo. [UserInfo]
where userid= @userid
Return
End
Go

The following is the ASP code:

' * * Call stored procedures that return multiple recordsets * *
DIM Checklg,userid,username,usertel,usermail
DIM Mycomm,myrst
UserID = 1
' Checklogin () is a custom function that determines whether the visitor is logged in
CHECKLG = Checklogin ()
Set Mycomm = Server.CreateObject ("Adodb.command")
With Mycomm

. ActiveConnection = Myconstr ' myconstr is a database connection string
. CommandText = "getuserlist" ' specifies stored procedure name
. CommandType = 4 ' indicates that this is a stored procedure
. Prepared = True ' requires that SQL commands be compiled in advance
. Parameters.Append. CreateParameter ("@userid", 3,1,4,userid)
. Parameters.Append. CreateParameter ("@checklogin", 11,1,1,CHECKLG)
Set Myrst =. Execute
End With
Set Mycomm = Nothing

' Take values from the first set of records
UserName = Myrst (0)
' Take values from the second recordset
If not Myrst are nothing Then
Set Myrst = Myrst.nextrecordset ()
Usertel = Myrst (0)
Usermail = Myrst (1)
End If
Set Myrst = Nothing

In the above code, a Recordset object's NextRecordset method is used to obtain multiple recordsets returned by the stored procedure.

So far, this article has made a more comprehensive explanation for the various cases of ASP calling stored procedures. And finally, in an ASP program, the different methods of calling multiple stored procedures.
In an ASP program, it is possible to invoke multiple stored procedures in at least three ways:
1. Create multiple Command objects.

DIM Mycomm
Set Mycomm = Server.CreateObject ("Adodb.command")
' Call a stored procedure
......
Set Mycomm = Nothing
Set Mycomm = Server.CreateObject ("Adodb.command")
' Call stored procedure two
......
Set Mycomm = Nothing
......

2. Create only one Command object and clear its arguments when the call is ended

DIM Mycomm
Set Mycomm = Server.CreateObject ("Adodb.command")
' Call a stored procedure
.....
' Clear parameters (assuming three parameters)
MyComm.Parameters.delete 2
MyComm.Parameters.delete 1
MyComm.Parameters.delete 0
' Call stored procedure two and clear parameters
......
Set Mycomm = Nothing

Note that the order of the purge parameters is the opposite of the order of the parameter declarations, and I don't know why.

3. Reset the Parameter object using the Refresh method of the Parameters data collection

DIM Mycomm
Set Mycomm = Server.CreateObject ("Adodb.command")
' Call a stored procedure
.....
' Resets all parameter objects contained in the Parameters data collection
MyComm.Parameters.Refresh
' Call stored procedure two
.....
Set Mycomm = Nothing

It is generally thought that creating objects repeatedly is a less efficient method, but after testing (the test tool is Microsoft Application Center test), the result is unexpected:
Method 2 >= Method 1 >> Method 3
Method 2 running speed is greater than or equal to Method 1 (up to 4% or so), the two methods are much faster than Method 3 (up to 130%), so the proposed parameters for a long time, using Method 1, in less than the parameter, the method 2.

It took me a day to finally put my words into some superficial experience of invoking stored procedures in ASP. Some of these are the only things I know about them, and some of them may be wrong, but these are the things I've practiced. Everyone reader to accept it critically. There are different opinions, I hope you will point out to me, thank you first.