There are many articles on ASP and stored procedures (Stored Procedures), but I doubt that the authors have actually practiced it. I read a lot of relevant information when I was in the beginning and found that many of the methods provided were not the case in practice. For simple applications, these materials may be helpful, but only because they are simply the same, plagiarizing each other, and slightly more complex applications will be vague.
Now, I basically access SQL Server by calling stored procedures. The following text is a summary of practice, and I hope it will be helpful to everyone.
A stored procedure is one or more SQL commands stored in the database as executable objects.
Definition is always abstract. Stored procedures are actually a set of SQL statements that can complete certain operations, but this set of statements is placed in the database (we will only talk about SQL Server here). 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 of doing this:
First, greatly improve efficiency. The stored procedure itself is very fast, and calling stored procedure can greatly reduce the number of interactions with the database.
Second, improve safety. If SQL statements are mixed in ASP code, once the code is lost, it means that the library structure is lost.
Third, it is conducive to the reuse of SQL statements.
In ASP, stored procedures are generally called through command objects. According to different situations, this article also introduces other calling methods. For convenience of explanation, the following simple classification is made based on the input and output of the stored procedure:
1. Only return stored procedures for a single record set
Suppose there is the following stored procedure (the purpose of this article is not to describe the T-SQL syntax, so the stored procedure only gives code and does not explain):
/*SP1*/
CREATE PROCEDURE
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 stored procedure through Command object**
DIM MyComm,MyRst
Set MyComm = ("")
= MyConStr 'MyConStr is the database connection string
= "getUserList" 'Specify the stored procedure name
= 4 ' indicates that this is a stored procedure
= true 'Require SQL command to be compiled first
Set MyRst =
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 property indicates the type of the request, the value and description are as follows:
-1 indicates that the type of 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
You can also call stored procedures through the Connection object or Recordset object, the methods are as follows:
'**Calling procedure through Connection object**
DIM MyConn,MyRst
Set MyConn = ("")
MyConStr 'MyConStr is the database connection string
Set MyRst = ("getUserList",0,4) 'The last parameter break has the same meaning as CommandType
Set MyConn = Nothing
'**Calling stored procedure through Recordset object**
DIM MyRst
Set MyRst = ("")
"getUserList",MyConStr,0,1,4
'MyConStr is a database connection string, and the last parameter break means the same as CommandType.
Now, I basically access SQL Server by calling stored procedures. The following text is a summary of practice, and I hope it will be helpful to everyone.
A stored procedure is one or more SQL commands stored in the database as executable objects.
Definition is always abstract. Stored procedures are actually a set of SQL statements that can complete certain operations, but this set of statements is placed in the database (we will only talk about SQL Server here). 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 of doing this:
First, greatly improve efficiency. The stored procedure itself is very fast, and calling stored procedure can greatly reduce the number of interactions with the database.
Second, improve safety. If SQL statements are mixed in ASP code, once the code is lost, it means that the library structure is lost.
Third, it is conducive to the reuse of SQL statements.
In ASP, stored procedures are generally called through command objects. According to different situations, this article also introduces other calling methods. For convenience of explanation, the following simple classification is made based on the input and output of the stored procedure:
1. Only return stored procedures for a single record set
Suppose there is the following stored procedure (the purpose of this article is not to describe the T-SQL syntax, so the stored procedure only gives code and does not explain):
/*SP1*/
Copy the codeThe code is as follows:
CREATE PROCEDURE
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 stored procedure through Command object**
DIM MyComm,MyRst
Set MyComm = ("")
= MyConStr 'MyConStr is the database connection string
= "getUserList" 'Specify the stored procedure name
= 4 ' indicates that this is a stored procedure
= true 'Require SQL command to be compiled first
Set MyRst =
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 property indicates the type of the request, the value and description are as follows:
-1 indicates that the type of 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
You can also call stored procedures through the Connection object or Recordset object, the methods are as follows:
Copy the codeThe code is as follows:
'**Calling procedure through Connection object**
DIM MyConn,MyRst
Set MyConn = ("")
MyConStr 'MyConStr is the database connection string
Set MyRst = ("getUserList",0,4) 'The last parameter break has the same meaning as CommandType
Set MyConn = Nothing
Copy the codeThe code is as follows:
'**Calling stored procedure through Recordset object**
DIM MyRst
Set MyRst = ("")
"getUserList",MyConStr,0,1,4
'MyConStr is a database connection string, and the last parameter break means the same as CommandType.
12345Next pageRead the full text